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
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