How do I write a macro that can be used in all workbooks and can I assign it
to a button?

Below shows the typical, basic procedure to create a macro and assign it to a
button.

1) From the Tools menu, choose VBA Editor.
2) In the Project Explorer, locate a file you're working on, right-click the
VBAProject of that workbook, choose insert, then choose module.
3) In the new blank screen of the module which appeared, type a macro. e.g.

Sub hello()
Msgbox "hello world!"
End Sub

4) Close the window of the VBA Editor (go back to the spreadsheet)
5) From the View menu, choose Toolbars, then choose Forms.
6) Click the icon which represents a "Button" control. Place a button on the
worksheet.
7) You'll be asked to assign a macro to the button. Choose the macro you
just created. Click OK to finish.

If you want to learn more about this, I would recommend reading a book (e.g.
a "Step-by-Step" book from Microsoft Press on the Excel VBA topic.


Regards,
Edwin Tam

http://www.vonixx.com



"Jo" wrote:

How do I write a macro that can be used in all workbooks and can I assign it
to a button?

Edwin

That is great but I actually wanted a customised button in the toolbar that
I could use in all excel spreadsheets

"Edwin Tam" wrote:

Below shows the typical, basic procedure to create a macro and assign it to a
button.

1) From the Tools menu, choose VBA Editor.
2) In the Project Explorer, locate a file you're working on, right-click the
VBAProject of that workbook, choose insert, then choose module.
3) In the new blank screen of the module which appeared, type a macro. e.g.

Sub hello()
Msgbox "hello world!"
End Sub

4) Close the window of the VBA Editor (go back to the spreadsheet)
5) From the View menu, choose Toolbars, then choose Forms.
6) Click the icon which represents a "Button" control. Place a button on the
worksheet.
7) You'll be asked to assign a macro to the button. Choose the macro you
just created. Click OK to finish.

If you want to learn more about this, I would recommend reading a book (e.g.
a "Step-by-Step" book from Microsoft Press on the Excel VBA topic.


Regards,
Edwin Tam

http://www.vonixx.com



"Jo" wrote:

How do I write a macro that can be used in all workbooks and can I assign it
to a button?

It's OK, I just figured it out.

Thansk

"Jo" wrote:

How do I write a macro that can be used in all workbooks and can I assign it
to a button?

How did you solve this?
I have posted my question but, not getting a response.
I have a template toolbar macro which renames the spreadsheet using the
value of cell a12. My macro is called 'SaveToFolder'.
IF I type 'BJS1' into cell a12 and click the macro, I have BJS1.xls saved to
my hard drive. If I look at the macro to see what it's assigned to it says
'BJS1.xls', not 'SaveToFolder'.

Any idea?

"Jo" wrote:

It's OK, I just figured it out.

Thansk

"Jo" wrote:

How do I write a macro that can be used in all workbooks and can I assign it
to a button?