Disabling and re-enabling the Cell Shortcut menu

[Peter Rooney]

Good morning all,
I use the following code to disable the cell chortcut menu:

Sub CellMenuCustomize()
Dim SCCellMenu As CommandBar
On Error Resume Next
On Error GoTo 0
Set SCCellMenu = CommandBars("Cell")
SCCellMenu.Reset
SCCellMenu.Enabled = False
End Sub

I use the following code to re-enable it:

Sub CellMenuReset()
Application.CommandBars("Cell").Enabled = True
Application.CommandBars("Cell").Reset
End Sub

However, when I run the disabling code and right click on a cell, I see a
small pale blue square with nothing written in it, and when I run the
enabling code, I STILL only see the square - the menu doesn't reset.
Can anyone tell me what I'm doing wrong?
Thanks in advance.

Pete

 

[Ron de Bruin]

Hi Peter

The enabling code must work

I think you xlb is corrupt ?
*********************
Maybe you have a corrupt or bloated xlb file *normal* size is < 30 kb.
The .xlb file has all Toolbar customization in it.

1. Close Excel
2. Do a search for .xlb in Windows (Use: search hidden files and folders)
3. Rename or delete the .xlb file or files (In 2002 the name = Excel10.xlb)
4. Start Excel

Deleting the file or renaming will do no harm on your system
Excel will create a new one for you.
(You lost your customization remember that)

If you make your own toolbars or add buttons to the others
this file is important (backup it so you can restore it)

 

[Peter Rooney]

Ron,

The only xlb file I have is "Symbols.xlb" which is in my SAP program file
folder.
Other than that, I've scanned both my hard drive and there's nothing there.
I'm using Excel 2003.
Spooky!

Pete

 

[Ron de Bruin]

Search in hidden folders also Peter

 

[Peter Rooney]

Doh! Thank you.
I renamed the file Excel11.xlb to XExcel11.xlb and restarted Excel.
The toolbars (including customisations) were still there.
When I re-searched for xlb files, Excel11.xlb had NOT been re-created.
What's going on?

Pete

 

[Ron de Bruin]

Hi Peter

I test it in 2003 also and only see the new xlb after I change something in my menu's (like putting the menu's on two rows)

 

[Peter Rooney]

Ron,

I've isolated the problem to the procedure that calls the CellmenuCustomize
and CellMenuReset procedures.
If I run them on their own, they work fine.
If I create new procedures to call them, they work fine.
They just don't work fine when called from the procedures that they need to
be called from (if that makes sense).
Customizing leaves the pale blue square where the shortcut menu should be
and resetting doesn't work at all.
I'll have to take a look at the calling procedures. If I find out what it
was, I'll let you know.
Thanks for your help :O)
Pete

 

[Peter Rooney]

Ron,

Is there a seperate Cell shortcut menu for each worksheet in a workbook, or
should disabling it anywhere disable it for all worksheets?

Thanks in advance

Pete

 

[Ron de Bruin]

There is one cell menu Peter

I have some code here to change it
http://www.rondebruin.com/menuid.htm#certain

Scroll to the Cell examples

 

[Peter Rooney]

Ron,

I was hoping I could say "I know" before you told me..!

The problem was being caused by some faulty worksheet_activate code in
another worksheet that was re-enabling the Cell menu, but not resetting it.
It was visible, but without any menu options - hence the small blue square.
I wish my life came with an "Undo" button..! Still, I learned quite a lot
today, so one green tick for you.
Thank you very much for your help! :O)
Pete

 

[Dave Peterson]

I've backed up my toolbar file (*.xlb) to a nice safe location.

When I screw it up (or someone else does), I can just close excel and copy that
backup file into its real location.

It saves me sometime when I'm destroying, er, fixing things.