Sculpt

When we added a custom command bar we hard-coded our values for the dropdown control. In reality, we’d want to avoid this: hard-coding would reduce the life-span of the workbook and make it very inflexible.

This example demonstrates a simply way of looping through a static array variable to retrieve the values. It assumes we simply want to use the initial 5 items for our dropdown – this can be amended as required. The example also contains hard-coded array bounds (1 to 5) – when developing a workbook, these should not be hard-coded for the same reasons we mentioned above.

The code below declares a static array variable, looping through the array, getting our values from a the ‘Data’ worksheet, then adding each value to the items in dropdown Cmb.
_______________________________
Sub GetValues(Cmb)

Dim iIndex(1 To 5) As String ‘Declares the static array variable
Dim iCount As Integer
For iCount = 1 To 5
iIndex(iCount) = _
Format(ThisWorkbook.Worksheets(“Data”).Cells(iCount, 1), “Mmmm yyyy”)
With Cmb
.AddItem iIndex(iCount) ‘, iCount
End With
Next iCount

End Sub
_______________________________

To get these values into the Custom Command Bar, we’ll slightly amend our code from earlier. We’ll replace our hard-coded values with Call GetValues(Cmb) code – this will retrieve the values for each item from the code above.

_______________________________

Sub CustomToolbar()

Dim cbar As Object
Dim Cmb As Object
Dim SubMenu As Object
Dim NewCb As Object
Dim SubMenu1 As Object
Dim SubMenu2 As Object
Dim SubMenu3 As Object

On Error Resume Next

Set cbar = CommandBars.Add(Name:=”Sculpt”, Position:=msoBarTop, Temporary:=True)
cbar.Visible = True
cbar.Enabled = True

Dim NewMenu As Object
Set NewMenu = CommandBars(”Sculpt”)
With NewMenu
.Visible = True
.Enabled = True
.Controls.Add(Type:=msoControlPopup, before:=1).Caption = “Sculpt”
.Controls.Add(Type:=msoControlDropdown, before:=2).Caption = “Select period”
.Controls.Add(Type:=msoControlButton, before:=3).Caption = “Get data”
End With
‘Add Sub Menus to Sculpt

Set SubMenu = CommandBars(”Sculpt”).Controls(”Sculpt”)
With SubMenu
.TooltipText = “Select for further options”
.Controls.Add(Type:=msoControlPopup, before:=1).Caption = “Print”
.Controls.Add(Type:=msoControlButton, before:=2).Caption = “Get Budgets”
End With

Set Cmb = CommandBars(”Sculpt”).Controls(”Select period”)
With Cmb
.Width = 120
.Visible = True
.Enabled = True

‘Get the values for the dropdown from GetValues(Cmb)
Call GetValues(Cmb)
.ListIndex = 1 ‘Item to display in the dropdown list

‘.OnAction = “‘” & ThisWorkbook & “‘!SubName”
End With

Set NewCb = CommandBars(”Sculpt”).Controls(”Get Data”)
With NewCb
.Visible = True
.FaceId = 7433
End With

‘Now add sub-menu items
Set SubMenu1 = CommandBars(”Sculpt”).Controls(”Sculpt”).Controls(”Print”)
With SubMenu1
.Controls.Add(Type:=msoControlButton, before:=1).Caption = “All Records”
.Controls(”All Records”).FaceId = 109
.Controls.Add(Type:=msoControlButton, before:=2).Caption = “Active Records”
.Controls(”Active Records”).FaceId = 928
End With

Set SubMenu2 = CommandBars(”Sculpt”).Controls(”Get data”)
With SubMenu2
.OnAction = “‘” & ActiveWorkbook.Name & “‘!SubName”
End With

Set SubMenu3 = CommandBars(”Sculpt”).Controls(”Sculpt”).Controls(”Get Budgets”)
With SubMenu3
.OnAction = “‘” & ActiveWorkbook.Name & “‘!SubName”
End With

‘Begin groups
Application.CommandBars(”Sculpt”).Controls(”Sculpt”) _
.Controls(”Get budgets”).BeginGroup = True

Set NewMenu = CommandBars(”Sculpt”)
With NewMenu
.Protection = msoBarNoChangeVisible ‘Stops the user from deleting the command bar

End With

End Sub
_______________________________