Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Popup Menu / Shortcut Menu
In Access, I can create popup menus that I can link to a form or controls on
a form. Whenever I right click in that control, a popup menu appears that allows me to perform whatever functionality I code into it. I've looked in the Excel 2003 toolbar, but did not see a way to create or utilize this functionality. Is it available? If so, how do I implement it? Dale -- Email address is not valid. Please reply to newsgroup only. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Popup Menu / Shortcut Menu
Yes, you can do something similar to that. The first procedure create a
command bar named "MyBar". The next three procs are placeholders for real code. Sub CreateMenuItems() Dim CmdBar As Office.CommandBar Dim Ctrl As Office.CommandBarControl On Error Resume Next Application.CommandBars("MyBar").Delete On Error GoTo 0 Set CmdBar = Application.CommandBars.Add(Name:="MyBar", Position:=msoBarPopup, temporary:=True) Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton) With Ctrl .Caption = "Click Me AAA" .OnAction = "'" & ThisWorkbook.Name & "'!AAA" End With Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton) With Ctrl .Caption = "Click Me BBB" .OnAction = "'" & ThisWorkbook.Name & "'!BBB" End With Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton) With Ctrl .Caption = "Click Me CCC" .OnAction = "'" & ThisWorkbook.Name & "'!CCC" End With End Sub Sub AAA() MsgBox "AAA" End Sub Sub BBB() MsgBox "BBB" End Sub Sub CCC() MsgBox "CCC" End Sub The next question is how you want to trigger this command bar to display. The following code in the Sheet1 module will substitute "MyBar" for the default right-click command bar (named "Cell") if you right-click anywhere within the range A1:C10 on Sheet1. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing Then Cancel = True ' don't display default menu Application.CommandBars("MyBar").ShowPopup ' show ours Exit Sub End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... In Access, I can create popup menus that I can link to a form or controls on a form. Whenever I right click in that control, a popup menu appears that allows me to perform whatever functionality I code into it. I've looked in the Excel 2003 toolbar, but did not see a way to create or utilize this functionality. Is it available? If so, how do I implement it? Dale -- Email address is not valid. Please reply to newsgroup only. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Popup Menu / Shortcut Menu
Chip,
Exactly what I was looking for. In Access I can do this via the View-Toolbar-Customize menu, and build commandbars that reside in the database. I don't have to do the code, so I had forgotten about that option. Does the code creating the command bar have to be run every time my UserForm is loaded, or is a one-time event? Dale "Chip Pearson" wrote in message ... Yes, you can do something similar to that. The first procedure create a command bar named "MyBar". The next three procs are placeholders for real code. Sub CreateMenuItems() Dim CmdBar As Office.CommandBar Dim Ctrl As Office.CommandBarControl On Error Resume Next Application.CommandBars("MyBar").Delete On Error GoTo 0 Set CmdBar = Application.CommandBars.Add(Name:="MyBar", Position:=msoBarPopup, temporary:=True) Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton) With Ctrl .Caption = "Click Me AAA" .OnAction = "'" & ThisWorkbook.Name & "'!AAA" End With Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton) With Ctrl .Caption = "Click Me BBB" .OnAction = "'" & ThisWorkbook.Name & "'!BBB" End With Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton) With Ctrl .Caption = "Click Me CCC" .OnAction = "'" & ThisWorkbook.Name & "'!CCC" End With End Sub Sub AAA() MsgBox "AAA" End Sub Sub BBB() MsgBox "BBB" End Sub Sub CCC() MsgBox "CCC" End Sub The next question is how you want to trigger this command bar to display. The following code in the Sheet1 module will substitute "MyBar" for the default right-click command bar (named "Cell") if you right-click anywhere within the range A1:C10 on Sheet1. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing Then Cancel = True ' don't display default menu Application.CommandBars("MyBar").ShowPopup ' show ours Exit Sub End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... In Access, I can create popup menus that I can link to a form or controls on a form. Whenever I right click in that control, a popup menu appears that allows me to perform whatever functionality I code into it. I've looked in the Excel 2003 toolbar, but did not see a way to create or utilize this functionality. Is it available? If so, how do I implement it? Dale -- Email address is not valid. Please reply to newsgroup only. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is a shortcut menu? | Excel Discussion (Misc queries) | |||
Alt Shortcut from right-click menu | Excel Discussion (Misc queries) | |||
Help with shortcut menu in Excel | Excel Discussion (Misc queries) | |||
Non-functional Popup Menu Boxes | Charts and Charting in Excel | |||
shortcut menu | Excel Discussion (Misc queries) |