Custom toolbar adding and deleting

P

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
 
T

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top