Lost my Command Menus - Please Help

  • Thread starter Thread starter Chris Hankin
  • Start date Start date
C

Chris Hankin

Could someone please help with unexpected side-effect after running the
following code:

Dim oCtrl As Object

With Application.CommandBars("Cell")

'Clear the existing menus
For Each oCtrl In .Controls
oCtrl.Delete
Next oCtrl

End With

When I right-click on any cell in my worksheet, I am unable to bring up
a command pop-up menu. What I get is a small blue-coloured rectangle
instead of a command pop-up menu. I then press the Esc - key to remove
the blue-coloured rectangle.

I even tried right-clicking on a new worksheet and the same
blue-coloured rectangle keep appearing.

Could some please advise on how I can reverse this situation?

Kind regards,

Chris.

Live Long and Prosper :-)
 
Chris,
What about this from the immediate window:
Application.CommandBars("Cell").Reset

NickHK
 
Hi again Chris -

To solve the problem immediately:

If you used the following to turn off the shortcut menu, type the following
into the VBA Immediate Window (and press Enter to run it):
CommandBars("Cell").Enabled = True

If you used the oCtl.Delete approach to turn off the shortcut menu, type the
following into the VBA Immediate Window (and press Enter to run it):
CommandBars("Cell").Reset
----------------------------------------------
Insert either of these lines (as appropriate) at a strategic location in
your VBA code, such as in the Workbook_Close module (or sooner), so that the
menus will be re-enabled as soon as you're done with your application
(review the original post on the subject concerning re-enabling).

There's nothing worse than losing menus and commands ! Sorry I didn't
emphasize the re-enabling more aggresively. I hope it didn't turn out to be
too much of an adventure for you...
 
Thanks NickHK and Jay for all your help - very much appreciated. Sorry
It has taken so long to get back to you, but my son very sick and is in
hospital. He is only now getting better.

Cheers,

Chris.

Live Long and Prosper :-)
 
Back
Top