Disable insert rows/columns in Excel

August 8, 2010 at 05:36:01
Specs: Windows Vista

Can anyone help me, how to disable the Insert rows/columns in Excel 2007 VBA

I tried on my sub, but it did not work

With Application
Application.CommandBars("Insert").Controls(4).Enabled = False
End With


See More: Disable insert rows/columns in Excel

Report •

August 8, 2010 at 09:06:56

The object model for the menu system on Office 2007 has changed completely. You won't be able to use your current approach to changing the menus. I presume what you posted is what you used in Office 2003 or earlier.

You can start learning about the Office Ribbon (User Interface) in this article from MSDN.

This site has a Custom UI Editor that can be used to edit the xml files required for modifying the Ribbon.

Here is the sequence to get you started with a Ribbon customizing file in Excel:
A. Open Excel 2007
B. Save an empty workbook as an Excel Add-In with the xlam extension, name it something like CustomRibbon.xlsam and save it in C:\Documents and Settings\<User name>\Application Data\Microsoft\AddIns (or other drive if Windows not on C: drive)
C. Download the CustomUI editor
D. Open the UI editor and open the CustomRibbon.xlsam file
E. Follow MS instructions for creating a custom UI, which will add to the existing Ribbon interface.
F. When done, save it and close and reopen Excel, and the changes should appear.

This site has information on using the editor and gives some examples.

There are also add-ins that can be purchased that are said to automate the process. As I have only ever used the UI editor, I can't say how good they are, or whether they are worth the money.

If you are used to writing macros to make the changes in older versions of Office, you will likely be able to use the free UI editor approach.


Report •
Related Solutions

Ask Question