Can Add-in determine if macros enabled in another workbook?

[Anthony Berglas]

Ie. The add-in (ThisWorkbook) is enabled (otherwise the exercise is
pointless) but are the macros in the ActiveWorkbook enabled?

(I want to know this to warn the user about the fact that Excel 95
Dialogs do not work if the Active workbook has Macros and they are
disabled (even though those ActoveWorkbook macros are never actually
called). A bug in Excel 2007.)

Anthony

 

[Héctor Miguel]

hi, Anthony !

Ie. The add-in (ThisWorkbook) is enabled (otherwise the exercise is pointless)
but are the macros in the ActiveWorkbook enabled?
(I want to know this to warn the user about the fact that Excel 95 Dialogs
do not work if the Active workbook has Macros and they are disabled
(even though those ActoveWorkbook macros are never actually called). A bug in Excel 2007.)

one way...
the workbook for which you need to know it's macro-status needs to be the "activeworkbook"...
(tested for xl 97 to 2007)

Function ActiveWorkbookMacroStatus() As Boolean
ActiveWorkbookMacroStatus = _
Application.CommandBars("exit design mode").Visible
End Function

Sub Ask4MacroStatus()
MsgBox "Macro execution for:" & vbCr & _
ActiveWorkbook.Name & vbCr & "are " & _
IIf(ActiveWorkbookMacroStatus, "Dis", "En") & "abled"
End Sub

hth,
hector.

 

[Anthony Berglas]

hi, Anthony !


one way...
the workbook for which you need to know it's macro-status needs to be the"activeworkbook"...
(tested for xl 97 to 2007)

Function ActiveWorkbookMacroStatus() As Boolean
  ActiveWorkbookMacroStatus = _
    Application.CommandBars("exit design mode").Visible
End Function

Thanks for that, looks like a classic hack. But I could not make it
work in ANY version of Excel (it only needs to work onXL07 for me).
It is false always, unless I explicitly make the commandbar visible,
in which case it is true always.

I also tried Application.Run (ThisWorkbook.Name & "!
aaDummyMethodToRun"); but this always works even if the ActiveWorkbook
is not runnable.

One hack that should work is to send keystrokes to the dialog and see
if a method is invoked. But that is very ugly and somewhat unstable.

Anthony

 

[Héctor Miguel]

hi, Anthony !

... I could not make it work in ANY version of Excel (it only needs to work onXL07 for me).
It is false always, unless I explicitly make the commandbar visible, in which case it is true always.

I also tried Application.Run (ThisWorkbook.Name & "!aaDummyMethodToRun")
but this always works even if the ActiveWorkbook is not runnable.

One hack that should work is to send keystrokes to the dialog and see if a method is invoked.
But that is very ugly and somewhat unstable.

- I created a xl-2007 workbook (plain & simple) and set it's property "IsAddIn" to True (hidden window just for test)
- this (new) workbook contains both procedures (the function and the sub "ask4macrostatus")
- I opened another workbook (either 2007 or 97-2003 version) with macros

- *IF* I choose NOT to enable the opening workbook macros...
(note that I can't see the "exit design mode" commandbar but...)
- when I call/run (from the hidden workbook) the sub "ask4macrostatus"...
(for an addin or hidden window workbook) you must...
{alt} + {F8} and TYPE the full-path to the procedure (i.e.) -> book1!module1.ask4macrostatus
- the msg shows... "... Disabled"

- *IF* I choose YES to enable the opening workbook macros... the msg shows... "... Enabled"

- other ways to "call" the funcion (and or a similar procedure like "ask4macrostatus")
(I guess that...) should be structured within *your* AddIn (sub/sub/...)procedures -?-

(as I said...) it worked (for me) from versions xl-97 to xl-2007
hth,
hector.

__ previous __