Can't Disable the "Insert - Rows" menu option in Excel using VBA m

[Guest]

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

 

[Ron de Bruin]

Hi

See
http://www.rondebruin.com/menuid.htm

Sub test()
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=296, Recursive:=True).Enabled = False
End Sub

 

[Guest]

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!

 

[Guest]

Specifically I'm looking for the control Id for Insert - Column

 

[Dave Peterson]

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.

 

[Guest]

Thanks, perfect.
--
PK9

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.

 

[Ron de Bruin]

Good morning

Look on my site