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