Custom toolbar adding and deleting

[Palpha32]

I have a custom toolbar which I want available only when a particular
worksheet is displayed. After searching the forums I came up with the
following code -

Private Sub Workbook_Activate()
CreateMenu
End Sub
Private Sub Workbook_Open()
CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Calculate").Delete
On Error GoTo 0
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("Calculate").Delete
On Error GoTo 0
End Sub

Sub CreateMenu()

Dim cmdToolbar As CommandBar
Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
Temporary:=True)

With cmdToolbar
..Position = msoBarTop
..left = (Application.CommandBars("Formatting").left +
Application.CommandBars("Formatting").Width)
..RowIndex = Application.CommandBars("Formatting").RowIndex
..Visible = True
End With

With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
..FaceId = 5
..Caption = "Calculate"
..Style = msoButtonIconAndCaption
..OnAction = "Cutting35"
End With

End Sub
Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Calculate").Delete
End Sub

This works as it should when I manually run it from the VBA editor but won't
run when I open, close, deactivate or activate the workbook. What am I doing
wrong?

Also, this is a protected workbook with protected worksheets so I will have
to unprotect the sheet and protect it again.

Thanks
Peter

 

[Tim Zych]

This slight modification worked for me.

Private Sub Workbook_Activate()
CreateMenu
End Sub
Private Sub Workbook_Open()
CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
Private Sub Workbook_Deactivate()
Call DeleteMenu
End Sub

Sub CreateMenu()

Call DeleteMenu

Dim cmdToolbar As CommandBar
Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
Temporary:=True)

With cmdToolbar
..Position = msoBarTop
..Left = (Application.CommandBars("Formatting").Left +
Application.CommandBars("Formatting").Width)
..RowIndex = Application.CommandBars("Formatting").RowIndex
..Visible = True
End With

With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
..FaceId = 5
..Caption = "Calculate"
..Style = msoButtonIconAndCaption
..OnAction = "Cutting35"
End With
End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Calculate").Delete
End Sub

 

[Palpha32]

Thanks Tim, however it is still the same for me - it doesn't add or remove
the toolbar when I open or close the workbook or switch between open
workbooks.

Peter

 

[Tim Zych]

The code must go in ThisWorkbook module.

 

[Palpha32]

Tricky ins't it! That works perfectly, thanks Tim

Peter