|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com