Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Self-updating shortcut menu
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... |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is a shortcut menu? | Excel Discussion (Misc queries) | |||
Popup Menu / Shortcut Menu | Excel Discussion (Misc queries) | |||
shortcut menu | Excel Discussion (Misc queries) | |||
Shortcut menu on worksheet tab | Excel Programming | |||
shortcut for menu | Excel Programming |