Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Customizing popup menus
Hello,
I have an Excel/Word VBA app and I need to be able to customize the popup (right click) menu on an Excel tab so I can add a custom command to insert my own custom worksheet. I can only find ways to alter the regular menu bar, but not the popup. Can anyone tell me how to do so linked to an item to perform a macro or sub routine. Is there a place to edit such menus or does it need to be done with code, and if the latter, how? Thanks, God bless Van |
#2
|
|||
|
|||
Van,
Here is some code to add to the menu. Sub CreateRightClick() With Application.CommandBars("Cell"*) With .Controls.Add .Caption = "Remove" .OnAction = "Remove" End With With .Controls.Add .Caption = "Remove2" .OnAction = "Remove2" End With End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "VanS" wrote in message ... Hello, I have an Excel/Word VBA app and I need to be able to customize the popup (right click) menu on an Excel tab so I can add a custom command to insert my own custom worksheet. I can only find ways to alter the regular menu bar, but not the popup. Can anyone tell me how to do so linked to an item to perform a macro or sub routine. Is there a place to edit such menus or does it need to be done with code, and if the latter, how? Thanks, God bless Van |
#3
|
|||
|
|||
Van
Must be done through code. Example to add a menu item to right-click which runs a macro. Sub Workbook_Open() With Application.CommandBars("Cell").Controls.Add(tempo rary:=True) .BeginGroup = True .Caption = "Clear Formats" .OnAction = "MyMacros.xla" & "!ClearFormatting" End With End Sub Good idea to make sure you clear the item if already on the right-click menu before adding it or you will get duplicates. Do this by adding a delete line. Revised code will look like this. Sub Workbook_Open() 'or _Activate Application.CommandBars("Cell").Controls("Clear Formats").Delete With Application.CommandBars("Cell").Controls.Add(tempo rary:=True) .BeginGroup = True .Caption = "Clear Formats" .OnAction = "MyMacros.xla" & "!ClearFormatting" End With End Sub Gord Dibben Excel MVP On Fri, 11 Mar 2005 14:03:06 -0800, "VanS" wrote: Hello, I have an Excel/Word VBA app and I need to be able to customize the popup (right click) menu on an Excel tab so I can add a custom command to insert my own custom worksheet. I can only find ways to alter the regular menu bar, but not the popup. Can anyone tell me how to do so linked to an item to perform a macro or sub routine. Is there a place to edit such menus or does it need to be done with code, and if the latter, how? Thanks, God bless Van |
#4
|
|||
|
|||
Van
No provision made for deleting the item when switching workbooks. Try this amended. Private Sub Workbook_Activate() With Application.CommandBars("Cell").Controls.Add(tempo rary:=True) .BeginGroup = True .Caption = "Clear Formats" .OnAction = "MyMacros.xla" & "!ClearFormatting" End With End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").Controls("Clear Formats").Delete End Sub Gord On Fri, 11 Mar 2005 16:04:35 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Van Must be done through code. Example to add a menu item to right-click which runs a macro. Sub Workbook_Open() With Application.CommandBars("Cell").Controls.Add(tempo rary:=True) .BeginGroup = True .Caption = "Clear Formats" .OnAction = "MyMacros.xla" & "!ClearFormatting" End With End Sub Good idea to make sure you clear the item if already on the right-click menu before adding it or you will get duplicates. Do this by adding a delete line. Revised code will look like this. Sub Workbook_Open() 'or _Activate Application.CommandBars("Cell").Controls("Clear Formats").Delete With Application.CommandBars("Cell").Controls.Add(tempo rary:=True) .BeginGroup = True .Caption = "Clear Formats" .OnAction = "MyMacros.xla" & "!ClearFormatting" End With End Sub Gord Dibben Excel MVP On Fri, 11 Mar 2005 14:03:06 -0800, "VanS" wrote: Hello, I have an Excel/Word VBA app and I need to be able to customize the popup (right click) menu on an Excel tab so I can add a custom command to insert my own custom worksheet. I can only find ways to alter the regular menu bar, but not the popup. Can anyone tell me how to do so linked to an item to perform a macro or sub routine. Is there a place to edit such menus or does it need to be done with code, and if the latter, how? Thanks, God bless Van |
#5
|
|||
|
|||
Thanks Bob. Will check it out.
God bless Van "Bob Phillips" wrote: Van, Here is some code to add to the menu. Sub CreateRightClick() With Application.CommandBars("Cell"Â*) With .Controls.Add .Caption = "Remove" .OnAction = "Remove" End With With .Controls.Add .Caption = "Remove2" .OnAction = "Remove2" End With End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "VanS" wrote in message ... Hello, I have an Excel/Word VBA app and I need to be able to customize the popup (right click) menu on an Excel tab so I can add a custom command to insert my own custom worksheet. I can only find ways to alter the regular menu bar, but not the popup. Can anyone tell me how to do so linked to an item to perform a macro or sub routine. Is there a place to edit such menus or does it need to be done with code, and if the latter, how? Thanks, God bless Van |
#6
|
|||
|
|||
Gord,
Many thanks for your help. Your suggestions are quite helpful. God bless Van "Gord Dibben" wrote: Van No provision made for deleting the item when switching workbooks. Try this amended. Private Sub Workbook_Activate() With Application.CommandBars("Cell").Controls.Add(tempo rary:=True) .BeginGroup = True .Caption = "Clear Formats" .OnAction = "MyMacros.xla" & "!ClearFormatting" End With End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").Controls("Clear Formats").Delete End Sub Gord On Fri, 11 Mar 2005 16:04:35 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Van Must be done through code. Example to add a menu item to right-click which runs a macro. Sub Workbook_Open() With Application.CommandBars("Cell").Controls.Add(tempo rary:=True) .BeginGroup = True .Caption = "Clear Formats" .OnAction = "MyMacros.xla" & "!ClearFormatting" End With End Sub Good idea to make sure you clear the item if already on the right-click menu before adding it or you will get duplicates. Do this by adding a delete line. Revised code will look like this. Sub Workbook_Open() 'or _Activate Application.CommandBars("Cell").Controls("Clear Formats").Delete With Application.CommandBars("Cell").Controls.Add(tempo rary:=True) .BeginGroup = True .Caption = "Clear Formats" .OnAction = "MyMacros.xla" & "!ClearFormatting" End With End Sub Gord Dibben Excel MVP On Fri, 11 Mar 2005 14:03:06 -0800, "VanS" wrote: Hello, I have an Excel/Word VBA app and I need to be able to customize the popup (right click) menu on an Excel tab so I can add a custom command to insert my own custom worksheet. I can only find ways to alter the regular menu bar, but not the popup. Can anyone tell me how to do so linked to an item to perform a macro or sub routine. Is there a place to edit such menus or does it need to be done with code, and if the latter, how? Thanks, God bless Van |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create menus in excel worksheet | Excel Worksheet Functions | |||
"Why did we get here????" popup | Excel Discussion (Misc queries) | |||
Non-functional Popup Menu Boxes | Charts and Charting in Excel | |||
Customizing ToolBars | Excel Discussion (Misc queries) | |||
Adding drop down menus to a spreadsheet | Excel Worksheet Functions |