Hello,
I am implementing a command bar that allows users to select any worksheet from the dropdown menu in this bar, and also let user to go home, go to the next page, or previous page with a button click. I accomplished all these, however, when users select next page, previous page, or home or even just click on the tab, I would like the sheet name to appears on this dropdown menu as well. Anybody please shows me the solution..
Thanks!
****This is the code that I have so far*****
Private Sub CommandButton2_Click()
On Error Resume Next
'Just in case another exists, delete it.
Application.CommandBars("N
avigate").
Delete
MsgBox (ActiveWorkbook.ActiveShee
t.Name)
On Error GoTo 0
'Add New Navbar.
With Application.CommandBars.Ad
d("Navigat
e", , False, False)
'Set position
With Application.CommandBars("N
avigate")
If .Position <> msoBarTop Then .Position = msoBarTop
End With
'Create Home button
With .Controls.Add(msoControlBu
tton)
.TooltipText = "Show Graph Page"
.FaceId = 1016
.OnAction = "'" & ThisWorkbook.Name & "'!Sheet1.Go_Home"
.BeginGroup = True
End With
'Create left arrow button
With .Controls.Add(msoControlBu
tton)
.TooltipText = "Previous Report"
.FaceId = 1017
.OnAction = "'" & ThisWorkbook.Name & "'!Sheet1.Sheet_Before"
.BeginGroup = True
End With
'Create Drop Down list box of Worksheet Names
With .Controls.Add(msoControlDr
opdown)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
.AddItem ws.Name
End If
Next ws
.Width = 200
.TooltipText = "SheetNavigation"
.OnAction = "'" & ThisWorkbook.Name & "'!Sheet1.SN"
End With
'Create right arrow button
With .Controls.Add(msoControlBu
tton)
.TooltipText = "Next Report"
.FaceId = 1018
.OnAction = "'" & ThisWorkbook.Name & "'!Sheet1.Next_Sheet"
End With
.Protection = msoBarNoCustomize
.Visible = True
End With
End Sub
Public Sub Go_Home()
'Activate First sheet
Worksheets("Sheet1").Activ
ate
End Sub
Public Sub SN()
'Sheet Navigation - Activates selected sheet.
Dim stActiveSheet As String
'Choose sheet from drop down
With CommandBars.ActionControl
stActiveSheet = .List(.ListIndex)
End With
'Activate chosen sheet
Worksheets(stActiveSheet).
Activate
End Sub
Public Sub Sheet_Before()
'Go to previous sheet
On Error Resume Next
ActiveSheet.Previous.Selec
t
End Sub
Public Sub Next_Sheet()
'Go to next sheet
On Error Resume Next
ActiveSheet.Next.Select
End Sub
Start Free Trial