Creating an Add-In that runs a macro

[jaws1972]

I have a macro that formats an Excel Spreadsheet, by adding the filename and date/time to the footer

Personally I have it saved in my Personal.xls, and is tied into a custom button on my toolbar

I have been asked to role it out to everyone in the Company (over 100 users) and I am a bit reluctant to go to each individual user to do this.

I have heard it is possible to turn the macro into an Add-In, which I have sort of done (and I mean sort of !!)

What I don't have is the first part of the add-in. The part that will create a custom button, copy the macro and save it to the users Personal.xls, and attach the macro to the button

Can anyone help me please

 

[robcarr]

this came out of help
i used application.commandbars

Set newItem = CommandBars("Tools&quot.Controls.Add(Type:=msoControlButton)
With newItem
.BeginGroup = True
.Caption = "Make Report"
.FaceID = 0
.OnAction = "qtrReport"
End With

regards

Thanks Rob.

 

[jaws1972]

Cheers Rob

Any ideas how I would bind the formatting macro to this ?

 

[robcarr]

qtrreport in the example is the macro to be run on the button, just replace with your own macro

Thanks Rob.

 

[SBendBuckeye]

Alternatively, you might want to consider Alternate Startup File Location on Tools, Options, General tab. Anything in that directory is automatically opened by Excel. You might think of it as giving you the ability to have a network, global personal.xls file.

If you put your code in a file on a network drive that was referenced there, everyone would have it immediately. If you forget and put another file there, it can look pretty interesting when Excel tries to open it.

This is an extremely low tech solution. Good Luck!


Have a great day!

j2consulting@yahoo.com