Hi,

My addin's Workbook_Open procedure, which runs when I load it via
Tools/Addins, does the following:

1) Adds a custom menu to the Worksheet Menu Bar
2) Adds command buttons to the custom menu
3) Disables some of those buttons unless the *active workbook* contains
a named range called "secret_word" that refers to a, well, secret word.

Trouble is, the Workbook_Open procedure doesn't seem to recognize the
active workbook (what I consider to be the workbook that was active
when I loaded the addin) as the application's activeworkbook property.
In fact, it recognizes as application.activeworkbook the just-opened
ADDIN, not the active workboook; this causes my step (3) above to
produce undesired results.

If you're still with me, create a new blank workbook, save it as
'text.xla', open up the vba editor and place the following code in
test.xla's Workbook_Open procedure. Save test.xla, then close it and
then, with a regular workbook open, load the 'Test' addin via
Tools/Addins. As it loads, you'll get a message stating that the
activeworkbook property of the application object is named 'test.xla'.
Next, Unload the addin. Now, reload it. This time the execution of the
code should stop; hit continue (F5) in the VBA editor, and you should
get a message indicating that activeworkbook is 'Book1' or whatever
workbook you have open in the Excel application.

Maybe there's a sensible explanation for why the procedure behaves
differently when execution is temporarily halted. In any case, I need a
way to grab the name of the active workbook while my addin is
opening/loading. Unless I put that Stop statement in my code (which
isn't going to work for my users) I can't seem to do it.

Private Sub Workbook_Open()

Dim FoundFlag As Boolean
Dim objStopSwitch As Name
Dim Message As String

' Search for workbook name "StopSwitch" and flag if found
FoundFlag = False
For Each nm In ThisWorkbook.Names
If nm.Name = "StopSwitch" Then
FoundFlag = True
Exit For
End If
Next

' If found, set objStopSwitch = workbook name
' If not found, set objStopSwitch = new workbook name
If FoundFlag = True Then
Set objStopSwitch = ThisWorkbook.Names("StopSwitch")
Else
Set objStopSwitch = ThisWorkbook.Names.Add _
(Name:="StopSwitch", RefersTo:="=FALSE")
ThisWorkbook.Save
End If

' The RefersTo property of objStopSwitch determines whether
' execution stops or continues
If objStopSwitch.RefersTo = "=TRUE" Then Stop

' Display message box with name of activeworkbook
MsgBox "The activeworkbook property of the application object is
named " & _
ActiveWorkbook.Name

' Toggle the RefersTo property of objStopSwitch so that next time
addin is loaded,
' the Stop statement will (or won't, depending) execute
If objStopSwitch.RefersTo = "=TRUE" Then
objStopSwitch.RefersTo = "=FALSE"
Else
objStopSwitch.RefersTo = "=TRUE"
End If

' Save addin file with new StopSwitch value
ThisWorkbook.Save

End Sub

Is it really an add-in, or is it a regular workbook with an add-in's name?
When you saved it, did you just change the extension to .xla, or did you
actually save it as an add-in file type?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"martin" wrote in message
oups.com...
Hi,

My addin's Workbook_Open procedure, which runs when I load it via
Tools/Addins, does the following:

1) Adds a custom menu to the Worksheet Menu Bar
2) Adds command buttons to the custom menu
3) Disables some of those buttons unless the *active workbook* contains
a named range called "secret_word" that refers to a, well, secret word.

Trouble is, the Workbook_Open procedure doesn't seem to recognize the
active workbook (what I consider to be the workbook that was active
when I loaded the addin) as the application's activeworkbook property.
In fact, it recognizes as application.activeworkbook the just-opened
ADDIN, not the active workboook; this causes my step (3) above to
produce undesired results.

If you're still with me, create a new blank workbook, save it as
'text.xla', open up the vba editor and place the following code in
test.xla's Workbook_Open procedure. Save test.xla, then close it and
then, with a regular workbook open, load the 'Test' addin via
Tools/Addins. As it loads, you'll get a message stating that the
activeworkbook property of the application object is named 'test.xla'.
Next, Unload the addin. Now, reload it. This time the execution of the
code should stop; hit continue (F5) in the VBA editor, and you should
get a message indicating that activeworkbook is 'Book1' or whatever
workbook you have open in the Excel application.

Maybe there's a sensible explanation for why the procedure behaves
differently when execution is temporarily halted. In any case, I need a
way to grab the name of the active workbook while my addin is
opening/loading. Unless I put that Stop statement in my code (which
isn't going to work for my users) I can't seem to do it.

Private Sub Workbook_Open()

Dim FoundFlag As Boolean
Dim objStopSwitch As Name
Dim Message As String

' Search for workbook name "StopSwitch" and flag if found
FoundFlag = False
For Each nm In ThisWorkbook.Names
If nm.Name = "StopSwitch" Then
FoundFlag = True
Exit For
End If
Next

' If found, set objStopSwitch = workbook name
' If not found, set objStopSwitch = new workbook name
If FoundFlag = True Then
Set objStopSwitch = ThisWorkbook.Names("StopSwitch")
Else
Set objStopSwitch = ThisWorkbook.Names.Add _
(Name:="StopSwitch", RefersTo:="=FALSE")
ThisWorkbook.Save
End If

' The RefersTo property of objStopSwitch determines whether
' execution stops or continues
If objStopSwitch.RefersTo = "=TRUE" Then Stop

' Display message box with name of activeworkbook
MsgBox "The activeworkbook property of the application object is
named " & _
ActiveWorkbook.Name

' Toggle the RefersTo property of objStopSwitch so that next time
addin is loaded,
' the Stop statement will (or won't, depending) execute
If objStopSwitch.RefersTo = "=TRUE" Then
objStopSwitch.RefersTo = "=FALSE"
Else
objStopSwitch.RefersTo = "=TRUE"
End If

' Save addin file with new StopSwitch value
ThisWorkbook.Save

End Sub

It's a real add-in. I created a new, blank workbook, selected File/Save
as.., typed "Test" in the file name box and selected the last file type
from the list. It's in c:\documents and settings\...\microsoft\addins\.

A more robust approach would be to keep the add-in installed, use
Workbook_Open and Workbook_BeforeClose to create and destroy your menu, and
use application events such as App_WorkbookOpen, App_WorkbookActivate,
App_SheetActivate to turn on (enable) your menus, and
App_WorkbookBeforeClose, App_WorkbookDeactivate, App_SheetDeactivate to turn
off (disable) the menus.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"martin" wrote in message
oups.com...
It's a real add-in. I created a new, blank workbook, selected File/Save
as.., typed "Test" in the file name box and selected the last file type
from the list. It's in c:\documents and settings\...\microsoft\addins\.

Thanks. I do actually use application events to do the
enabling/disabling of the menus while the addin is installed. I've got
a locked, protected calendar template ("calendar.xls") that I use in
conjunction with the addin, and when calendar.xls is opened or
activated, application events are fired that unlock the range in
calendar.xls that I want users to enter data into. If the addin is not
installed, I don't want them to be able to do anything in calendar.xls.
I DO want the addin to recognize whether the active workbook is
calendar.xls (not by name, but by a hidden property) as it's being
installed and to enable/disable menu items accordingly. Likewise, as
the addin is being uninstalled, I want any open instances of
calendar.xls to be locked down. I don't expect my users to
install/uninstall the addin that frequently, but I want to control what
happens when they do.

Martin,

I've run into a very similar situation. I ended up putting a module-level
variable "active_workbook" in the addin's Workbook_AddinInstall procedure,
because at that time the addin was not yet the activeworkbook. I then
referred to "active_workbook" in the Workbook_Open procedure.

hth,

Doug


"martin" wrote in message
oups.com...
Hi,

My addin's Workbook_Open procedure, which runs when I load it via
Tools/Addins, does the following:

1) Adds a custom menu to the Worksheet Menu Bar
2) Adds command buttons to the custom menu
3) Disables some of those buttons unless the *active workbook* contains
a named range called "secret_word" that refers to a, well, secret word.

Trouble is, the Workbook_Open procedure doesn't seem to recognize the
active workbook (what I consider to be the workbook that was active
when I loaded the addin) as the application's activeworkbook property.
In fact, it recognizes as application.activeworkbook the just-opened
ADDIN, not the active workboook; this causes my step (3) above to
produce undesired results.

If you're still with me, create a new blank workbook, save it as
'text.xla', open up the vba editor and place the following code in
test.xla's Workbook_Open procedure. Save test.xla, then close it and
then, with a regular workbook open, load the 'Test' addin via
Tools/Addins. As it loads, you'll get a message stating that the
activeworkbook property of the application object is named 'test.xla'.
Next, Unload the addin. Now, reload it. This time the execution of the
code should stop; hit continue (F5) in the VBA editor, and you should
get a message indicating that activeworkbook is 'Book1' or whatever
workbook you have open in the Excel application.

Maybe there's a sensible explanation for why the procedure behaves
differently when execution is temporarily halted. In any case, I need a
way to grab the name of the active workbook while my addin is
opening/loading. Unless I put that Stop statement in my code (which
isn't going to work for my users) I can't seem to do it.

Private Sub Workbook_Open()

Dim FoundFlag As Boolean
Dim objStopSwitch As Name
Dim Message As String

' Search for workbook name "StopSwitch" and flag if found
FoundFlag = False
For Each nm In ThisWorkbook.Names
If nm.Name = "StopSwitch" Then
FoundFlag = True
Exit For
End If
Next

' If found, set objStopSwitch = workbook name
' If not found, set objStopSwitch = new workbook name
If FoundFlag = True Then
Set objStopSwitch = ThisWorkbook.Names("StopSwitch")
Else
Set objStopSwitch = ThisWorkbook.Names.Add _
(Name:="StopSwitch", RefersTo:="=FALSE")
ThisWorkbook.Save
End If

' The RefersTo property of objStopSwitch determines whether
' execution stops or continues
If objStopSwitch.RefersTo = "=TRUE" Then Stop

' Display message box with name of activeworkbook
MsgBox "The activeworkbook property of the application object is
named " & _
ActiveWorkbook.Name

' Toggle the RefersTo property of objStopSwitch so that next time
addin is loaded,
' the Stop statement will (or won't, depending) execute
If objStopSwitch.RefersTo = "=TRUE" Then
objStopSwitch.RefersTo = "=FALSE"
Else
objStopSwitch.RefersTo = "=TRUE"
End If

' Save addin file with new StopSwitch value
ThisWorkbook.Save

End Sub

Thanks - good idea, and glad I wasn't alone. Need documentation on the
order in which all these events fire. Similarly, through trial and
error I discovered that when you open (install) an addin, the
app_workbookopen event considers activeworkbook to be the active
workbook, not the addin. So in app_workbookopen, I added some code
starting with

If Wb.IsAddin then
if Wb.Name = <my addin's name then
ActiveWorkbookName = ActiveWorkbook.Name
...

Problem solved!