Conditional enabling of menu items

[Phil Hibbs]

I've made an add-on that creates a menu item, but I only want it to be
enabled if there is a spreadsheet open. No, it's worse than that, I
only want it enabled if there is a cell selected. Basically I want to
mimic the behaviour of the Format->Cells menu option. How do I detect
this status and dynamically enable and disable my menu item as these
things change?

Phil Hibbs.

 

[Patrick Molloy]

probably at the application event level

create a CLASS module & within this dim a variable like this:

Option Explicit
Private WithEvents xl As Excel.Application
Private Sub Class_Initialize()
Set xl = Excel.Application
End Sub
Private Sub Class_Terminate()
Set xl = Nothing
End Sub

now from the objects dropdown, select xl and the methods dropdown shows all
the available events
maybe you could use sheetactivate/deasctivate to show/hide your menu

I have this code in an XLA that loads whenever excel opens.
In a standard module I have this:

Option Explicit
Global Const BARNAME As String = "Useful"
Public xl As clsExcelApp
Sub Auto_Open()
Set xl = New clsExcelApp
End Sub

In the above code, clExcelApp is the name of my class module
the code add my 'uaseful' toolbar --- you already have code, so just call
that with the class methods as I mentioned earlier.

hope this is helpful

 

[Phil Hibbs]

OK I think I've got that working - but the xl_SheetDeactivate and
xl_SheetActivate get called when changing between tabs, but not when
closing or opening a workbook, or when focus moves off a cell and onto
a control (e.g. a button or an edit box). I need to work out which
events, if the necessary events are exposed.

Phil Hibbs.