XL 2003 Menu Question 1

[RonRepp]

Hi, all:

I have a few procedures that create a menu (called "Worksheets") and then adds the sheets of the active workbook. However, when I try to run it, when I click on the sheet name, I always get the last menu name.

Public Sub AddWorksheetsMenu()
Dim myMenuBar As CommandBar
Dim i As Integer
Set myMenuBar = CommandBars.ActiveMenuBar
Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
newMenu.Caption = "Worksheets"
For i = 1 To ActiveWorkbook.Worksheets.Count
    'MsgBox Worksheets(i).Name

'Caller = Worksheets(i).Name

Set ctrl1 = newMenu.CommandBar.Controls _
    .Add(Type:=msoControlButton, ID:=i)
With ctrl1
    .Caption = ActiveWorkbook.Worksheets(i).Name
    .TooltipText = ActiveWorkbook.Worksheets(i).Name
    .Style = msoButtonCaption
    .OnAction = ActiveWorkbook.Worksheets(i).Name '"GoToSheet"
End With
Next i

End Sub

Public Sub DeleteWorksheetsMenu()
    On Error Resume Next
    
    Application.CommandBars("Worksheet Menu Bar").Controls("Worksheets").Delete
End Sub

Public Sub GoToSheet()
Sheets(Caller).Select
End Sub

I realize WHY it's happening, but I don't know any other way around it, because the OnAction needs a string.

Any help will be greatly appreciated.

Thanks,

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[GlennUK]

Change your OnAction to be GotoSheet

Change your GotoSheet macro to be:

Public Sub GoToSheet()
Sheets(Application.Caller(1)).Select
End Sub

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[RonRepp]

GlennUK:

Thanks so much. A Star for you.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[GlennUK]

My pleasure!

Cheers, Glenn.

Beauty is in the eye of the beerholder.