Custom Toolbar: ListBox on.action not working

[Alison]

I'm working with Excel 2003 and have written a macro for a floating
custom toolbar with a listbox. When I step through the macros to add
the commandbar and to use the listbox information they both work.
However when I "call" the macro using the .OnAction property a message
box is displayed saying the macro cannot be found. They are both
stored in the same module. Here are the macros...

Sub SelectSheetMenu()
Dim SheetSlct As CommandBar
Dim SelShtCtrl As CommandBarControl
Dim SheetNames As Range

Application.ScreenUpdating = False
Sheets("Lookups").Select
Range("A2").Select
Set SheetNames = Range(Cells(ActiveCell.Row, 1),
Cells(ActiveCell.End(xlDown).Row, 1))

'Delete existing toolbar if it exists
On Error Resume Next
CommandBars("Select Sheet").Delete
On Error GoTo 0

'Create new toolbar
Set SheetSlct = Application.CommandBars.Add("Select Sheet",
msoBarFloating, False, True)
SheetSlct.Visible = True

'Add dropdown control
Set SelShtCtrl = SheetSlct.Controls.Add(msoControlDropdown, 1, , ,
True)
SelShtCtrl.Visible = True

With SelShtCtrl
.Caption = "Select Worksheet"
.DescriptionText = "Go to a selected worksheet"
.Width = 150
.OnAction = "SelectSheet"
' fill it with SheetNames
For Each cell In SheetNames
.AddItem cell.Text
Next cell
.ListIndex = 2
End With

With SelShtCtrl
SlctdSht = .List(.ListIndex)
Sheets(SlctdSht).Select
End With

End Sub

Sub SelectSheet()
'Activates selected sheet
Dim SlctdSht As String
With CommandBars("Select Sheet").Controls("Select Worksheet")
SlctdSht = .List(.ListIndex)
Sheets(SlctdSht).Select
End With
End Sub

Can anyone determine where I'm going wrong?

Alison

 

[Jim Cone]

Alison,

It worked for me in Excel 97.
However I had to declare two variables...
Dim Cell As Range
Dim SlctdSht As String

One thing you can try is to change...
.OnAction = "SelectSheet"
to
..OnAction = ThisWorkbook.Name & "!SelectSheet"

Regards,
Jim Cone
San Francisco, CA

 

[Alison Rogers]

Hi Jim

Thanks for the advice. I've made your suggested changes but it still
can't find the called macro. I've tried it on a colleagues PC who is
using Excel 2000 and it doesn't work on hers either.

Very strange...

Thanks for the suggestions.

Alison

 

[Jim Cone]

Alison,

Had another thought...
Your code should be in a "general" module - not in the sheet module or the workbook module?

Regards,
Jim Cone
San Francisco, CA