[Excel 2003] Error when running sub in error handler

[elleg]

So, the project I'm working on is an add-in that basically adds a mock windows taskbar to the bottom of the Excel application. It creates a custom toolbar and adds buttons for each workbook that's open, that will allow you to activate the workbooks with the buttons. When new workbooks are opened, new buttons are added for those workbooks, and when workbooks are closed, their buttons are deleted. It also refreshes the taskbar when you switch between workbooks, to try and avoid, "the problem".

"The problem" --the only problem, really, with it-- is that if you use 'Save As' and re-name a file, the toolbar does not update. It keeps the old name of the file on the button, and the button no longer works. I've included an error handler for the button action macro (ActivateWB is the sub name) which, right now, just pops up a message box saying that workbook is no longer available.

I've tried to call my Refresh procedure within that error handler to fix the broken button, but it always errors out. But, if the error handler is triggered, the message box displays, and the procedures ends, and then I go into the Refresh procedure and manually run it, it works fine with no errors. I can't figure out what the problem is, so if anyone has any suggestions, do share!

Below are the procedures involved in "the problem", and attached is a copy of my full add-in.

Sub ActivateWB(WBName As String) 'macro that runs when toolbar button is pressed
    Dim iResp As Integer
    ' "'" & "modProcedures.ActivateWB """ & wkbk.Name & """'"
    On Error GoTo e1
    Workbooks(WBName).Activate
    Exit Sub
e1:
    'workbook no longer exists or has had name change
    'need to refresh bar
    iResp = MsgBox("The workbook of that name is no longer available.", vbOKOnly + vbInformation, "Workbook Not Found:")
    'Call RefreshMockTaskBar 'This is what errors out every time
End Sub
 
Sub RefreshMockTaskBar()
    Dim cbctrl As CommandBarControl
    Dim cbMTB As CommandBar
    Dim cbcNew As CommandBarControl
    Dim sCBName As String
    Dim wkbk As Variant
    Dim bExists As Boolean
    
    sCBName = "MockTaskBar" 'my custom bar's name
 
    'Add a button for each open workbook, if not one already
    For Each wkbk In Workbooks
        If wkbk.Name <> "MockBar.xla" Then
            Call AddButton(wkbk.Name, sCBName)
        End If
    Next wkbk
    
    Set cbMTB = CommandBars(sCBName)
    
    'Remove any buttons that are no longer applicable
    For Each cbctrl In cbMTB.Controls
        bExists = False
        For Each wkbk In Workbooks
            If cbctrl.Caption = wkbk.Name Then
                bExists = True
            End If
        Next wkbk
        If bExists = False Then
            Call RemoveButton(cbctrl.Caption, sCBName)
        End If
    Next cbctrl
 
End Sub
 
Sub AddButton(WBName As String, CBName As String)
    Dim cbctrl As CommandBarControl
    Dim MyBar As CommandBar
    Dim cbcNew As CommandBarControl
    Dim sCBName As String
    Dim bExists As Boolean
    Dim wkbk As Variant
    
    If WBName <> "MockBar.xla" Then
        Call AddBar(CBName) 'add the bar if it doesn't already exist
        Set MyBar = CommandBars(CBName) 'set the bar to a variable
        
        bExists = False
        'check to see if a button exists for the wkbk
        For Each cbctrl In MyBar.Controls
            If cbctrl.Caption = WBName Then
                bExists = True
            End If
        Next cbctrl
        'if no button exists, add a button
        If bExists = False Then
            Set cbcNew = MyBar.Controls.Add(Type:=msoControlButton)
            With cbcNew
                .Style = msoButtonCaption
                .Caption = WBName
                .TooltipText = WBName
                .OnAction = "'" & "modProcedures.ActivateWB """ & .Caption & """'"
            End With
        End If
    End If
End Sub
 
Sub RemoveButton(WBName As String, CBName As String)
    Dim cbar As CommandBar
    Dim cbctrl As CommandBarControl
    Dim MyBar As CommandBar
    Dim bExists As Boolean
    Dim wkbk As Variant
    Dim iIndex As Long
    
    Call AddBar(CBName) 'add the bar if it doesn't already exist
    Set MyBar = CommandBars(CBName) 'set the bar to a variable
    
    bExists = False
    'check to see if a button exists for the wkbk & delete
    For Each cbctrl In MyBar.Controls
        If cbctrl.Caption = WBName Then
            bExists = True
            cbctrl.Delete
        End If
    Next cbctrl
End Sub

[westconn1]

as you do not say where you get the error, i presume it is when the button is being deleted
the error is because the button is still in pressed state, therefore cannot be deleted

workaround is, on error, to make the button not visible, it will be deleted next time the code is run anyway, so you will only ever have need to have 1 not visible button

[elleg]

Yes, the error happens when it tries to delete the button in the Refresh procedure. I'll take a look into hiding the button, potentially within an error trap in the delete procedure... Thanks for the suggestion.