How to create a button in the Excel toolbar for ...

[jamshoot]

Hi all

Can someone teach me how to create a button in the Excel toolbar so that when I click it, it will open a worksheet named "My WS.xls" residing in c:\My Folder\.

Cheers

 

[JamesW]

Hi there,

Create a new module and insert the following macro:

Sub openWB()
    Workbooks.Open Filename:="C:\My Folder\My WS.xls"
End Sub

(Alt + F11 -> Insert -> Module -> Paste the above)

Then in Excel, right click on any toolbar and click on "Customize". Scroll down to Macros and click and drag anywhere in the toolbar space either the Custom Button with the smily face or the one with just text.

Once dragged onto a toolbar, right click on the new button and click "Assign Macro" and find your "openWB" macro you created earlier.

You can create a new image for your button, and set it to show Text and Image if you want.

 

[jamshoot]

Hi JamesW

Thks for the quick reply. It works perfect.
Btw, u mentioned ... "You can create a new image for your button, and set it to show Text and Image if you want.", may I know how to do that.
Let say , I want to change the smiley round image & have text when the cursor hover on it ?

Cheers

 

[JamesW]

Not sure about changing the text when you hover over it, but for the other stuff:

Right click on the button, click on Customize, right click on the button again and click either Change button image (choose a default one) or edit button image (create your own).

Make sure Image and Text is selected also.

 

[jamshoot]

Hi JamesW

Great ... managed to change the icon & text.
The icon selection is quite limited. It would be great if anyone in this forum can share how to add or create customized icon.

Thanks JamesW

Cheers

 

[JamesW]

Edit button image will allow you to create your own. The more artistic you are, the better it will look (mine look like a 5 year old has gone mental with the mouse!)