Central Macro Workbook

[Nicholas Wautier]

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)

"Nicholas Wautier" <Nicholas [email protected]> wrote in
message news:[email protected]...
> 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.

[Nicholas Wautier]

Nice article, but it looks like I'd still need to manually install the macro
on each user's PC. Would an add-in file also contain custom toolbars?

"Bob Phillips" wrote:

>
> 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)
>
> "Nicholas Wautier" <Nicholas [email protected]> wrote in
> message news:[email protected]...
> > 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]

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)

"Nicholas Wautier" <[email protected]> wrote in
message news:[email protected]...
> Nice article, but it looks like I'd still need to manually install the
macro
> on each user's PC. Would an add-in file also contain custom toolbars?
>
> "Bob Phillips" wrote:
>
> >
> > 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)
> >
> > "Nicholas Wautier" <Nicholas [email protected]> wrote in
> > message news:[email protected]...
> > > 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.
> >
> >
> >