Trying to disable a custom commandbar button

[Steve Jacobs]

Within a module I have created a custom toolbar, and added a button to it:

Sub CreateBar()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("NavBar").Delete
Set ComBar = CommandBars.Add(Name:="NavBar", Position:=msoBarFloating,
Temporary:=True)
ComBar.Visible = True
' Create a button with text & icon on the bar and set some properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton,
Parameter:="GoBackBtn")
With ComBarContrl
.Caption = "Go &Back"
.Style = msoButtonIconAndCaption
.TooltipText = "Go back to the previously viewed worksheet"
'the onaction line tells the button to run a certain macro
.OnAction = "btnGoBack"
.Width = "85"
.FaceId = 41
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

In my Workbook_open event, I call the above 'CreateBar' sub.

In ThisWorkbook, in Workbook_SheetActivate, I call the 'enable_back' function:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'MsgBox ("The selected worksheet has changed!" & Sh.Name)
On Error Resume Next

If Sh.Name <> "NavIndex" Then
ThisWorkbook.Worksheets("NavIndex").Range("A1").Insert Shift:=xlDown
ThisWorkbook.Worksheets("NavIndex").Range("A1") = Sh.Name
End If

If ThisWorkbook.Worksheets("NavIndex").UsedRange.Rows.Count > 1 Then
enable_back
' ComBarContrl.Enabled

End Sub

Enable_back is in the same module as CreateBar, and looks like this:

Public Function enable_back()

CommandBars("NavBar").Controls("GoBackBtn").Enabled = True

End Function

I DON'T SEE ERRORS when enable_back is called.

In the same module is disable_back. Disable_back is identical to
enable_back, except that .Enabled = False.

I have a macro in the module that calls disable_back. Whenever disable_back
runs, I get the error "Run-time error '5': Invalid procedure call or
argument".

I haven't figured out how to fix this, but I'm very new to this and suspect
this must be something simple.

Thanks,
Steve

 

[Steve Jacobs]

Within a module I have created a custom toolbar, and added a button to it:

Sub CreateBar()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("NavBar").Delete
Set ComBar = CommandBars.Add(Name:="NavBar", Position:=msoBarFloating,
Temporary:=True)
ComBar.Visible = True
' Create a button with text & icon on the bar and set some properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton,
Parameter:="GoBackBtn")
With ComBarContrl
.Caption = "Go &Back"
.Style = msoButtonIconAndCaption
.TooltipText = "Go back to the previously viewed worksheet"
'the onaction line tells the button to run a certain macro
.OnAction = "btnGoBack"
.Width = "85"
.FaceId = 41
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

In my Workbook_open event, I call the above 'CreateBar' sub.

In ThisWorkbook, in Workbook_SheetActivate, I call the 'enable_back' function:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'MsgBox ("The selected worksheet has changed!" & Sh.Name)
On Error Resume Next

If Sh.Name <> "NavIndex" Then
ThisWorkbook.Worksheets("NavIndex").Range("A1").Insert Shift:=xlDown
ThisWorkbook.Worksheets("NavIndex").Range("A1") = Sh.Name
End If

If ThisWorkbook.Worksheets("NavIndex").UsedRange.Rows.Count > 1 Then
enable_back
' ComBarContrl.Enabled

End Sub

Enable_back is in the same module as CreateBar, and looks like this:

Public Function enable_back()

CommandBars("NavBar").Controls("GoBackBtn").Enabled = True

End Function

I DON'T SEE ERRORS when enable_back is called.

[ IGNORE THIS PART - I had error handling code affecting that. ]