Self-updating shortcut menu

[Guest]

Hello, I hope someone can help me with this, PLEASE.

I have built a program that when the user double clicks in any sheet, a shortcut menu is displayed listing all of the sheet names in the active workbook. This much I have and works great.

But now, I need the ability to capture which sheet name the user clicks on the shortcut menu and then take the user to the SAME ROW on the sheet name the user clicked.

The number and names of the sheets varies all the time, so the shortcut menu must be dynamic.

For example, user is in Sheet5 and double clicks on row 455, the shortcut menu is displayed and the user selects Sheet89. The program takes the user to sheet89 and selects row 455.

How can I do this?

As I said my program already captures the sheet names and puts them into a menu, but how do I determine which sheet name the user clicked? My code so far follows:

Dim arrSheets() As Variant
Dim objSheet As Worksheet
Dim cmdBar As CommandBar
Dim intX As Integer
Dim varItem As Variant
Dim strButtonName As String

For Each objSheet In ThisWorkbook.Sheets
If objSheet.Visible = True Then
intX = intX + 1
ReDim Preserve arrSheets(intX)
arrSheets(intX) = objSheet.Name
End If
Next

For intX = 1 To UBound(arrSheets)
strButtonName = arrSheets(intX)
Set cmdBar = Application.CommandBars(cstrShortCutMenu4)
With cmdBar
.Controls.Add Type:=msoControlButton
.Controls(intX).Caption = strButtonName
.Controls(intX).OnAction = "GOTO_Program"
End With
Next intX

Your example code would be most appreciated. Thanks in advance...

 

[Tom Ogilvy]

Public Sub GOTO_Program()
Dim rw as Long, sName as String
rw = Activecell.row
sName = CommandBars.ActionControl.Caption
worksheets(sName).Select
cells(rw,1).Select
' or Rows(rw).Select
End sub

--
Regards,
Tom Ogilvy

Hello, I hope someone can help me with this, PLEASE.

I have built a program that when the user double clicks in any sheet, a

shortcut menu is displayed listing all of the sheet names in the active
workbook. This much I have and works great.

But now, I need the ability to capture which sheet name the user clicks on

the shortcut menu and then take the user to the SAME ROW on the sheet name
the user clicked.

The number and names of the sheets varies all the time, so the shortcut menu must be dynamic.

For example, user is in Sheet5 and double clicks on row 455, the shortcut

menu is displayed and the user selects Sheet89. The program takes the user
to sheet89 and selects row 455.

How can I do this?

As I said my program already captures the sheet names and puts them into a

menu, but how do I determine which sheet name the user clicked? My code so
far follows:

 

[Guest]

Amazing

Thanks Tom.