I have the following code in "ThisWorkbook" module.
I get the "Delete' of object '-CommandBarButton' failed" error when I utilize Windows(ThisWorkbook.Name).Close from Module1...but not if I manually close the file.
What am I missing?
Rich (BB code):
Private Sub Workbook_Open()
Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl
'Delete the control first to avoid duplicates
Set ContextMenu = Application.CommandBars("Cell")
'Delete custom controls with the Tag : My_Cell_Control_Tag
For Each ctrl In ContextMenu.Controls
If ctrl.Tag = "My_Cell_Control_Tag" Then
ctrl.Delete
End If
Next ctrl
'Set ContextMenu to the Cell menu
Set ContextMenu = Application.CommandBars("Cell")
'Add one custom button to the Cell menu
With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
.OnAction = "'" & ThisWorkbook.Name & "'!CreateDisplayPopUpMenu"
.FaceId = 59
.Caption = "Create Reports"
.Tag = "My_Cell_Control_Tag"
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl
'Set ContextMenu to the Cell menu
Set ContextMenu = Application.CommandBars("Cell")
'Delete custom controls with the Tag : My_Cell_Control_Tag
For Each ctrl In ContextMenu.Controls
If ctrl.Tag = "My_Cell_Control_Tag" Then
ctrl.Delete
End If
Next ctrl
On Error Resume Next
Application.CommandBars(Mname).Delete
On Error GoTo 0
End Sub
What am I missing?
Last edited: