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

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" wrote:

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

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" wrote:

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" wrote:

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

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
procedu

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


"Bishop" wrote:

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" wrote:

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" wrote:

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