Using Command Buttons for Navigation in Excel

How to use command buttons on a splash screen in MS-Excel
How can I create buttons on a splash screen that take you to another sheet in the workbook? When I reach the next worksheet I do not wish to see the column and row header numbers and the horizontal and vertical scrollbars. How do I achieve this? How can I exit the workbook with a single click?

We use two command buttons with the following VBA code to solve this problem:

Private Sub CommandButton1_Click()
Sheets(“sheet2”).Select
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End With
End Sub

Private Sub CommandButton2_Click()

‘if you don’t wish to receive a message to save the file use the code below

ThisWorkbook.Save=True

ThisWorkbook.Close

End Sub

You can also rename the command buttons appropriately by right-clicking on them and edting their properties.

Watch the video below to see how to use command buttons on a splash screen in MS-Excel


One thought on “Using Command Buttons for Navigation in Excel”

  1. Thanks for sharing this amazing tutorial sir.

    I Need to ask you a question. Lets suppose that I am have 470 Sheets through wish i wish to navigate using a single command button whose value can be selected from a Four-Tier Cascading Listbox that I have created. (This is basically a product category selector)

    Secondly, I wish to include CASE function since the value in listbox do not match with the Sheet name that I have created.

    Third, How can I Hide all worksheets at once except the one that I will be selecting using this Navigation tool that we will be creating.

    Sir Hope you can help me figure out the solution to this problem.
    Thanks in advance !!

Leave a Reply

Your email address will not be published. Required fields are marked *