With Microsoft Excel in mainstream use, it is heavily used for crunching data and other tasks. One of the main irritations about the software is the many tabs people created to categorize their information. Too many tabs creates the bottom portion of the screen to shorten because not too many names can be shown at once. It is also somewhat frustrating when users such as me have to move the mouse down to the bottom screen portion just to click the arrows and scroll through the different names.
To turn that problem around, I went through the user-form version in Excel VBA for creating a navigation window dialog. That dialog will help me to display the tab names in three empty boxes. I use arrow buttons on the dialog box to scroll through the different work tabs of a workbook and then display the names in three empty boxes of the dialog window. Scrolling from right to left or vice-versa helps me to go through subsequent and previous tabs easily with the dialog in the middle screen and I don’t have to move my mouse way down. I click on one of the three boxes containing the sheet name I want to go to and I will be arriving at that worksheet tab. This process through this dialog will make it faster to copy or paste data in different tabs quickly.
This video shows how the process is done:
After watching this video, here are the lines of codes that run my macro so you can put it for your user-form to navigate through the tabs:
For the first command button, these are the codes:
***************************************************
Private Sub CommandButton1_Click()
m = CommandButton1.Caption
If IsEmpty(m) Or m = “” Then
MsgBox “There is no sheet name in the selected box.”
Else
ThisWorkbook.Sheets(m).Select
End If
End Sub
***************************************************
For the second command button, these set:
***************************************************
Private Sub CommandButton2_Click()
m = CommandButton2.Caption
If IsEmpty(m) Or m = “” Then
MsgBox “There is no sheet name in the selected box.”
Else
ThisWorkbook.Sheets(m).Select
End If
End Sub
***************************************************
For the third command button, here is this set:
***************************************************
Private Sub CommandButton3_Click()
m = CommandButton3.Caption
If IsEmpty(m) Or m = “” Then
MsgBox “There is no sheet name in the selected box.”
Else
ThisWorkbook.Sheets(m).Select
End If
End Sub
***************************************************
For the arrow codes, here they are:
***************************************************
Private Sub SpinButton1_Change()
X = 1
Dim wa(1200000, 1)
For Each ht In ThisWorkbook.Worksheets
wa(X, 1) = ht.Name
X = X + 1
Next ht
m = SpinButton1.Value
dd = wa(3, 1)
CommandButton1.Caption = wa(m, 1)
CommandButton2.Caption = wa(m + 1, 1)
CommandButton3.Caption = wa(m + 2, 1)
Dim t As Worksheets
End Sub
***************************************************
For the closing command, these are the codes:
***************************************************
Private Sub CommandButton4_Click()
Unload UserForm2
Dim ht As Worksheets
End Sub
***************************************************
Hope these code sets run well on your Excel software and improve your productivity. If you have any problems, please e-mail me or leave comments below so i can answer them.
To connect with me on the media platform, click through these:
https://www.facebook.com/ExcelQualityProgramCustomize34?ref=aymt_homepage_panel
To see my other videos, click here:
https://www.youtube.com/channel/UCdhZjVbM-pS3sL1iqtjyDgQ
To pay for my services so I can solve your complex Microsoft Excel issues:
go here: https://www.fiverr.com/towcal/accelerate-excel-work-through-macros-and-vba
And… to see my other articles at this site, hover here: