Sculpt

Custom command bars are useful in helping users perform functions integral to your workbook. If there is only one function, you might just add a basic command button to a worksheet. However, with more complex workbooks, you should provide users with a readily available command bar to perform all functions.

The following example adds a custom command bar with a sub-menu and a drop-down list. The command bar type is temporary: you can secure it while the workbook is open, but this will automatically be deleted on workbook close.

______________________

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

‘Set the values in the dropdown – hardcoded for this example

.AddItem “November 2006″, 1
.AddItem “December 2006″, 2
.AddItem “January 2007″, 3
.AddItem “February 2007″, 4
.AddItem “March 2007″, 5
.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

______________________

The above code will create a custom command bar like this:

You can select dropdown values from the command bar dropdown:

______________________

You can get more info on custom command bars here.