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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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!
 
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.
 
Thanks, perfect.
--
PK9


Dave Peterson said:
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.
Click to expand...
 
Back
Top