Add-in AND Macros Not Showing Up in New Book

S

SkyEyes

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?
 
J

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?
 
S

SkyEyes

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?
 
J

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?
 
S

SkyEyes

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??
 
S

SkyEyes

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
 
D

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?
 
S

SkyEyes

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.
 
D

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.
Click to expand...
 
S

SkyEyes

Yes. thanks that was it. Works perfectly.

Dave Peterson said:
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
Click to expand...
 
Top