Initializing a custom toolbar

[Bishop]

I have the following custom toolbar and button assignment. It works if I
manually go through the code but I want it to initialize automatically when
the spreadsheet is opened. How do I do this?

Sub CatalystDumpToolBar()

Dim CDToolBar As CommandBar

Set CDToolBar = CommandBars.Add(temporary:=True)
With CDToolBar
.Name = "CDToolBar"
.Position = msoBarTop
.Visible = True
End With
End Sub

Sub AddCustomControl()

Dim CBar As CommandBar
Dim CTTally As CommandBarControl

Set CBar = CommandBars("CDToolBar")
Set CTTally = CBar.Controls.Add(Type:=msoControlButton)

With CTTally
.FaceId = 1763
.OnAction = "CatalystToTally"
End With
CBar.Visible = True

End Sub

 

[ProfessionalExcel.com]

In the ThisWorkbook module, place the following code:

Private Sub Workbook_Open()

Call CatalystDumpToolBar

Call AddCustomControl

End Sub


--
Please rate this post if it answers your question.

Thanks,

Chris
http://www.ProfessionalExcel.com

 

[Bishop]

I need to add a check to see if the toolbar is already present. If it is
then no need for the call. If it isn't then I need to call the two
procedures. How do I do this?

 

[ProfessionalExcel.com]

Change the procedure to:

Private Sub Workbook_Open()
Dim intCounter As Integer

For intCounter = 1 To Application.CommandBars.Count
If Application.CommandBars(intCounter).Name = "CDToolBar" Then
Exit Sub
Next intCounter

Call CatalystDumpToolBar

Call AddCustomControl

End Sub


Also, if you want to delete the toolbar at any point, use the following
procedure:

Sub DeleteMenu()

Dim intCounter As Integer

For intCounter = Application.CommandBars.Count To 1 Step -1
If Application.CommandBars(intCounter).Name = "CDToolBar" Then
Application.CommandBars(intCounter).Delete
End If
Next intCounter

End Sub


----------------------------
Please rate this post if it answers your question.

Thanks,

Chris
http://www.ProfessionalExcel.com