PDA

View Full Version : Greyed Out Menu Items when Editing in Cell



markh1182
06-14-2007, 08:03 AM
Hi, I have code that adds a new item to the file menu and want to follow Excels standard for certain menu items where they get greyed out when you double click into a cell.

How do I do this?

My code to create the menu item is:
Code:
With Application.CommandBars.ActiveMenuBar.Controls("File")
With .Controls.Add(before:=10)
.Caption = "Import as New Version of Existing Spreadsheet"
.OnAction = "SaveAsNewVersionofiManageDoc"
End With
End With


It is contained in an Auto_Open macro at the moment.

geekgirlau
06-14-2007, 05:31 PM
As I see it, the problem is being able to trigger when the control is enabled/disabled. With built-in menu items, Excel controls this for you.

You could use the Workbook SheetBeforeDoubleClick event to set the Enabled property of the control to false, and then the Workbook SheetChange event to enable it again, but this doesn't deal with pressing F2 to edit a cell, or typing in a new cell. Unfortunately the Change event is triggered after you have finished editing, so this doesn't help.

Maybe someone else has a bright idea?

mikerickson
06-14-2007, 07:03 PM
This board seems to have no "Delete Message" for dumb ideas.

OK. An OnTime routine that runs every 10th of a second. It matches the enabled property of your control to that of a standard Excel control.

geekgirlau
06-14-2007, 08:39 PM
If it ran every 10th of a second you wouldn't be able to do anything else!

I think this one would need to be dealt with by the error handling in the procedure you're calling rather than trying to disable the menu item.

markh1182
06-15-2007, 03:54 AM
I've got the beforedoubleclick on sheetchange events working from within an addin. Anyone else any ideas on how to grey out an item when pressing F2 or you are in the middle of entering text?