Adding Dropdowns to the Toolbar

In addition to having command buttons on the toolbar, you can have a drop-down control. This example adds a drop-down with the names of all visible worksheets in the workbook. When the client selects a worksheet from the drop-down, the GoToSheet macro navigates to that sheet. This concept can be extended to reporting certain regions or any such activity that involves grouping of objects or items. Add the following code to the existing CreateToolbar macro:

Dim Sh as Worksheet

Set NewDD = TBar.Controls.Add(Type:=msoControlDropdown) With NewDD

.Caption = "Go To Sheet" .OnAction = "GoToSheet" .Style = msoButtonAutomatic For Each Sh In ActiveWorkbook.Worksheets If Sh.Visible = True Then

.Addltem Sh.Name End if Next Sh .Listlndex = 1 .Width = 110 End With

Sub GoToSheet()

Dim ThisItem as Integer Dim ThisName as String

With CommandBars("XYZ").Controls("Go To Sheet") ThisItem = .Listlndex ThisName = .List(.Listlndex) End With

'Activate this sheet Worksheets(ThisName).Activate End Sub

0 0

Post a comment