Central Macro Workbook

[Guest]

Is there any way to have one central workbook of macros for an entire office?
I am writing a series of formating and quoting macros for my office, but
will not be able to go to each person's computer to copy the code and make
the nessicary toolbar additions. Even if we don't "share" a work book, I
need to find a way to get the code distributed, any and all suggestions would
be much appreciated!

P.S. My office is still using Office 2000 SP3.

 

[Bob Phillips]

http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Nice article, but it looks like I'd still need to manually install the macro

 

[Bob Phillips]

It can do. The normal approach is to add the toolbars on workbook_open. Here
is some simple code example, but as you say, it does need installing, but
that is simple, and can be done from a server.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar",
temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I have a similar situation.

I created a template with multiple sheets and it is accessible on a network
P2P drive. I created the macros and toolbar and the add-in and saved the .xlt
and addin file to a folder accessible to all. When I start the .xlt from
another machine I get the toolbar fine but the macros are not visible when I
alt-F8. Do I need to export them to somewhere from the computer Im creating
them on?

Andrew