Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Disable the "Insert - Rows" menu option in Excel using VBA m
I have a macro for which I do some conditional checks in the SelectionChange
event for a worksheet. When one of my conditions evaluates to true I'm trying to do the following: *) Disable the Insert - Rows menu option (from the main menu bar at the top of Excel) I have tried many different things. The closest I can get is to disable the "Insert" control entirely (which is not acceptable). I only want to disable the "Insert Rows" option. The following code disables the entire "Insert" control on the Worksheet Menu Bar: Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False I CAN'T figure out how to disable the Rows option underneath the Insert control though!! Any help would be greatly appreciated. Thanks -- PK9 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Disable the "Insert - Rows" menu option in Excel using VBA m
Hi
See http://www.rondebruin.com/menuid.htm Sub test() Application.CommandBars("Worksheet Menu Bar").FindControl _ (ID:=296, Recursive:=True).Enabled = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "PK9" wrote in message ... I have a macro for which I do some conditional checks in the SelectionChange event for a worksheet. When one of my conditions evaluates to true I'm trying to do the following: *) Disable the Insert - Rows menu option (from the main menu bar at the top of Excel) I have tried many different things. The closest I can get is to disable the "Insert" control entirely (which is not acceptable). I only want to disable the "Insert Rows" option. The following code disables the entire "Insert" control on the Worksheet Menu Bar: Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False I CAN'T figure out how to disable the Rows option underneath the Insert control though!! Any help would be greatly appreciated. Thanks -- PK9 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Disable the "Insert - Rows" menu option in Excel using V
YOU ARE A LIFESAVER!!! Thank you so much. That is exactly what I want.
Do you have a list of the control id's (like 296 in this example)?? I may need to do this for other sub-menu items. Thanks again! -- PK9 "Ron de Bruin" wrote: Hi See http://www.rondebruin.com/menuid.htm Sub test() Application.CommandBars("Worksheet Menu Bar").FindControl _ (ID:=296, Recursive:=True).Enabled = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "PK9" wrote in message ... I have a macro for which I do some conditional checks in the SelectionChange event for a worksheet. When one of my conditions evaluates to true I'm trying to do the following: *) Disable the Insert - Rows menu option (from the main menu bar at the top of Excel) I have tried many different things. The closest I can get is to disable the "Insert" control entirely (which is not acceptable). I only want to disable the "Insert Rows" option. The following code disables the entire "Insert" control on the Worksheet Menu Bar: Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False I CAN'T figure out how to disable the Rows option underneath the Insert control though!! Any help would be greatly appreciated. Thanks -- PK9 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Disable the "Insert - Rows" menu option in Excel using V
Specifically I'm looking for the control Id for Insert - Column
-- PK9 "Ron de Bruin" wrote: Hi See http://www.rondebruin.com/menuid.htm Sub test() Application.CommandBars("Worksheet Menu Bar").FindControl _ (ID:=296, Recursive:=True).Enabled = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "PK9" wrote in message ... I have a macro for which I do some conditional checks in the SelectionChange event for a worksheet. When one of my conditions evaluates to true I'm trying to do the following: *) Disable the Insert - Rows menu option (from the main menu bar at the top of Excel) I have tried many different things. The closest I can get is to disable the "Insert" control entirely (which is not acceptable). I only want to disable the "Insert Rows" option. The following code disables the entire "Insert" control on the Worksheet Menu Bar: Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False I CAN'T figure out how to disable the Rows option underneath the Insert control though!! Any help would be greatly appreciated. Thanks -- PK9 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Disable the "Insert - Rows" menu option in Excel using V
Hit alt-f11 to get to the VBE
hit ctrl-g to get to the immediate window type this in and hit enter: ?Application.CommandBars("worksheet menu bar").Controls("Insert") _ .Controls("columns").ID I get 297 back. PK9 wrote: Specifically I'm looking for the control Id for Insert - Column -- PK9 "Ron de Bruin" wrote: Hi See http://www.rondebruin.com/menuid.htm Sub test() Application.CommandBars("Worksheet Menu Bar").FindControl _ (ID:=296, Recursive:=True).Enabled = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "PK9" wrote in message ... I have a macro for which I do some conditional checks in the SelectionChange event for a worksheet. When one of my conditions evaluates to true I'm trying to do the following: *) Disable the Insert - Rows menu option (from the main menu bar at the top of Excel) I have tried many different things. The closest I can get is to disable the "Insert" control entirely (which is not acceptable). I only want to disable the "Insert Rows" option. The following code disables the entire "Insert" control on the Worksheet Menu Bar: Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False I CAN'T figure out how to disable the Rows option underneath the Insert control though!! Any help would be greatly appreciated. Thanks -- PK9 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Disable the "Insert - Rows" menu option in Excel using V
Thanks, perfect.
-- PK9 "Dave Peterson" wrote: Hit alt-f11 to get to the VBE hit ctrl-g to get to the immediate window type this in and hit enter: ?Application.CommandBars("worksheet menu bar").Controls("Insert") _ .Controls("columns").ID I get 297 back. PK9 wrote: Specifically I'm looking for the control Id for Insert - Column -- PK9 "Ron de Bruin" wrote: Hi See http://www.rondebruin.com/menuid.htm Sub test() Application.CommandBars("Worksheet Menu Bar").FindControl _ (ID:=296, Recursive:=True).Enabled = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "PK9" wrote in message ... I have a macro for which I do some conditional checks in the SelectionChange event for a worksheet. When one of my conditions evaluates to true I'm trying to do the following: *) Disable the Insert - Rows menu option (from the main menu bar at the top of Excel) I have tried many different things. The closest I can get is to disable the "Insert" control entirely (which is not acceptable). I only want to disable the "Insert Rows" option. The following code disables the entire "Insert" control on the Worksheet Menu Bar: Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False I CAN'T figure out how to disable the Rows option underneath the Insert control though!! Any help would be greatly appreciated. Thanks -- PK9 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Disable the "Insert - Rows" menu option in Excel using V
Good morning
Look on my site -- Regards Ron de Bruin http://www.rondebruin.nl "PK9" wrote in message ... Specifically I'm looking for the control Id for Insert - Column -- PK9 "Ron de Bruin" wrote: Hi See http://www.rondebruin.com/menuid.htm Sub test() Application.CommandBars("Worksheet Menu Bar").FindControl _ (ID:=296, Recursive:=True).Enabled = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "PK9" wrote in message ... I have a macro for which I do some conditional checks in the SelectionChange event for a worksheet. When one of my conditions evaluates to true I'm trying to do the following: *) Disable the Insert - Rows menu option (from the main menu bar at the top of Excel) I have tried many different things. The closest I can get is to disable the "Insert" control entirely (which is not acceptable). I only want to disable the "Insert Rows" option. The following code disables the entire "Insert" control on the Worksheet Menu Bar: Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False I CAN'T figure out how to disable the Rows option underneath the Insert control though!! Any help would be greatly appreciated. Thanks -- PK9 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
No "Excel Services" option in Publish menu | Excel Discussion (Misc queries) | |||
Stop users from accessing "Protection" option from "Tools" menu | Excel Programming | |||
How to disable the "Insert Copied Cells" context menu item | Excel Programming | |||
Excel "Work"menu option | Excel Programming |