dit is wat ik zie als ik foutopsporing kies. Als ik in de sub Workbook_Open() de regel uitschakel die de foutmelding geeft, dan geeft vervolgens de volgende regel een foutmelding.
Option Explicit
Dim WithEvents App As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cb As CommandBarButton, cmb As Object
On Error Resume Next
Set cmb = Application.CommandBars("Worksheet Menu Bar").Controls("File").CommandBar
Set cb = cmb.FindControl(Tag:="AJK-XSFmt")
cb.Delete
Set cmb = Application.CommandBars("Chart Menu Bar").Controls("File").CommandBar
Set cb = cmb.FindControl(Tag:="AJK-XSFmt")
cb.Delete
Application.CommandBars("Worksheet Menu Bar").Controls("File").FindControl(ID:=3).BeginGroup = True
Application.CommandBars("Chart Menu Bar").Controls("File").FindControl(ID:=3).BeginGroup = True
End Sub
Private Sub Workbook_Open()
Dim cb As CommandBarButton, cmb As Object
'Set cmb = Application.CommandBars("Chart Menu Bar").Controls("File").CommandBar
Set cb = cmb.FindControl(ID:=3)
cb.BeginGroup = False
Set cb = cmb.Controls.Add(Type:=msoControlButton, before:=cb.Index, Temporary:=True)
cb.OnAction = "'" & ThisWorkbook.Name & "'!ClearExcessRowsAndColumns"
cb.Caption = "Clear Excess Rows/Columns"
cb.Tag = "AJK-XSFmt"
cb.BeginGroup = True
Set cmb = Application.CommandBars("Worksheet Menu Bar").Controls("File").CommandBar
Set cb = cmb.FindControl(ID:=3)
cb.BeginGroup = False
Set cb = cmb.Controls.Add(Type:=msoControlButton, before:=cb.Index, Temporary:=True)
cb.OnAction = "'" & ThisWorkbook.Name & "'!ClearExcessRowsAndColumns"
cb.Caption = "Clear Excess Rows/Columns"
cb.Tag = "AJK-XSFmt"
cb.BeginGroup = True
MenuCaption
Set App = Excel.Application
End Sub
Private Sub App_WorkbookActivate(ByVal wb As Workbook)
MenuCaption
End Sub
Private Sub App_WorkbookDeactivate(ByVal wb As Workbook)
MenuCaption True
End Sub
Private Sub MenuCaption(Optional HideMenu As Boolean = False)
Static cb As CommandBarButton, cb1 As CommandBarButton
Dim cmb As Object
On Error Resume Next
If cb Is Nothing Then
Set cmb = Application.CommandBars("Worksheet Menu Bar").Controls("File").CommandBar
Set cb = cmb.FindControl(Tag:="AJK-XSFmt")
Set cmb = Application.CommandBars("Chart Menu Bar").Controls("File").CommandBar
Set cb1 = cmb.FindControl(Tag:="AJK-XSFmt")
End If
If ActiveWorkbook Is Nothing Or HideMenu Then
cb.Visible = False
cb1.Visible = False
Exit Sub
End If
cb.Visible = True
cb.Caption = "Clear Excess Formats in " & ActiveWorkbook.Name
cb1.Visible = True
cb1.Caption = "Clear Excess Formats in " & ActiveWorkbook.Name
End Sub