Add-in AND Macros Not Showing Up in New Book

[Guest]

Had a macro and saved the blank workbook as an excel add-in. Added to
c:\documents and settings\USER\application
data\microsoft\addins\nameoffile.xla on the workstation under that userid.

Opened excel and turned on Tools | Addins | selected the addin.

The macros do not show in if I look at Tool | Macros. We have had this
problem with our addins since moving to Office 2003. These worked fine on
the workstation with Office 2000. Any ideas?

 

[Jim Cone]

Macros with arguments, macros declared as Private, macros in a module
with an Option Private Module statement will not appear in Tools | Macro | Macros.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"SkyEyes" <[email protected]>
wrote in message
Had a macro and saved the blank workbook as an excel add-in. Added to
c:\documents and settings\USER\application
data\microsoft\addins\nameoffile.xla on the workstation under that userid.
Opened excel and turned on Tools | Addins | selected the addin.
The macros do not show in if I look at Tool | Macros. We have had this
problem with our addins since moving to Office 2003. These worked fine on
the workstation with Office 2000. Any ideas?

 

[Guest]

This is a public module with NO arguments. Again we place in the directory
below and if you go into macros, the macro does not show. Again if I place
this back on a machine with Office 2000 in the same directory, the macro
shows up just fine. Something since we have moved to Office 2003. Group
policies or such perhaps? What plays in the macros showing? If I go into
Alt F11 (VBA), click on Tools | Macros in that VBAProject, I can see the
ConnecttoDB macro just fine. Just not from within book1 or whatever?

 

[Jim Cone]

The only thing I can add is...
that the default behavior for xl97 thru xl2003 is to NOT show the name
of procedures in Tools | Macro | Macros, if the workbook is an add-in.
A workbook can have an .xla extension and not be an add-in.
(the "IsAddin" property must be set to true)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"SkyEyes" <[email protected]>
wrote in message
This is a public module with NO arguments. Again we place in the directory
below and if you go into macros, the macro does not show. Again if I place
this back on a machine with Office 2000 in the same directory, the macro
shows up just fine. Something since we have moved to Office 2003. Group
policies or such perhaps? What plays in the macros showing? If I go into
Alt F11 (VBA), click on Tools | Macros in that VBAProject, I can see the
ConnecttoDB macro just fine. Just not from within book1 or whatever?

 

[Guest]

Where do I set this. If I go into the code (actually open just the XLA file,
and go into VBA, I cannot locate where you set the property to an add-in).
HELP??

 

[Jim Cone]

In the VBE, you can press F4 or go to the menu: View | Properties window.
If you have the correct workbook (project) selected, then selecting "ThisWorkbook"
displays the properties for the workbook. They are in alphabetical order.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"SkyEyes" <[email protected]>
wrote in message
Where do I set this. If I go into the code (actually open just the XLA file,
and go into VBA, I cannot locate where you set the property to an add-in).
HELP??

 

[Guest]

Yes...this was set to true. Does the macro have to be attached to a toolbar
or should I be able to open another workbook and select Tools | Macros and
see the macro from the add-in? It is not hidden/protected or anything. That
is why I'm wondering if there is a setting.

We've going through all the documentation on creating an excel add-in and
according to this documentation we are doing everything fine.

 

[Gord Dibben]

Add-in macros will not show up in Tools>Macro>Macros.

Not in version 2000 or any other version.

That is a fact of Excel life.

If the macros show up in that list, your add-in is not an add-in.


Gord Dibben MS Excel MVP

 

[Jim Cone]

Even though the macro name does not appear in the Macros list,
you can still enter the macro name and click Run to run the code.

On the other hand, the usual way is to have code in the add-in that
creates a button/toolbar when the add-in is opened and removes
the button/toolbar when the add-in closes.
There are instructions here... http://www.contextures.on.ca/xlToolbar02.html
--
Jim Cone


"SkyEyes" <[email protected]>
wrote in message
Yes...this was set to true. Does the macro have to be attached to a toolbar
or should I be able to open another workbook and select Tools | Macros and
see the macro from the add-in? It is not hidden/protected or anything. That
is why I'm wondering if there is a setting.
We've going through all the documentation on creating an excel add-in and
according to this documentation we are doing everything fine.

 

[Guest]

Thanks..this is the piece we were missing that you have to set up a button on
a toolbar (build one on the fly or whatever) that is linked to that macro.
Works perfectly, except instead of AUTO_NEW had to utilize _OPEN.

Thanks again!!

 

[Dave Peterson]

Was there someplace that said you should use auto_new as the procedure name?

 

[Guest]

Option Explicit

Public Const ToolBarName As String = "MyToolbarName"
'===========================================
Sub Auto_Open()
Call CreateMenubar
End Sub

'===========================================
Sub Auto_Close()
Call RemoveMenubar
End Sub

'===========================================
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub

'===========================================
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant

Call RemoveMenubar

MacNames = Array("aaa", _
"bbb")

CapNamess = Array("AAA Caption", _
"BBB Caption")

TipText = Array("AAA tip", _
"BBB tip")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 71 + iCtr
.TooltipText = TipText(iCtr)
End With
Next iCtr
End With
End Sub

'===========================================
Sub AAA()
MsgBox "aaa"
End Sub

'===========================================
Sub BBB()
MsgBox "bbb"
End Sub

 

[Dave Peterson]

I don't see any Auto_New in that code.

You sure you didn't change it to match what you do in MSWord and then had to
change it back?

 

[Guest]

I apologize, it is Sub Auto_Open() which did not invoke the command to
create the toolbar in our environment until I changed it to just
workbook_Open(). I would think auto_open should work just fine. Also,
Workbook_BeforeClose() was utilized to get remove the toolbar. I tried on
several machines, finally switched to just workbook_open and beforeclose()
and now the add-in functions wonderfully. Thanks for everyone's assistance.
Not sure if this is the best thing, but it works every time.

 

[Dave Peterson]

The original code was supposed to be placed in a General module--not behind
ThisWorkbook (not behind a worksheet and not a class module).

If you pasted the code into the ThisWorkbook module, then you'd have to change
the names of the procedures. If it was pasted into a General module, then it
would have worked as written.

Debra Dalgleish's site has instructions with pictures that showed how to do it:
http://www.contextures.on.ca/xlToolbar02.html

I apologize, it is Sub Auto_Open() which did not invoke the command to
create the toolbar in our environment until I changed it to just
workbook_Open(). I would think auto_open should work just fine. Also,
Workbook_BeforeClose() was utilized to get remove the toolbar. I tried on
several machines, finally switched to just workbook_open and beforeclose()
and now the add-in functions wonderfully. Thanks for everyone's assistance.
Not sure if this is the best thing, but it works every time.

 

[Guest]

Yes. thanks that was it. Works perfectly.

The original code was supposed to be placed in a General module--not behind
ThisWorkbook (not behind a worksheet and not a class module).

If you pasted the code into the ThisWorkbook module, then you'd have to change
the names of the procedures. If it was pasted into a General module, then it
would have worked as written.

Debra Dalgleish's site has instructions with pictures that showed how to do it:
http://www.contextures.on.ca/xlToolbar02.html