Assigning macro to a toolbar

V

Vicky9063

Board Regular
Joined
Oct 27, 2005
Messages
79
Hi,
Do you know how to assign a macro to a toolbar that can be reused by someone else on another computer? I seem to have trouble getting the toolbar to come back with the custom buttons when i open it on another computer. Is there a way to do it purely in VBA code? Can i use commandbuttons on a toolbar?
Thanks!
V
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Good afternoon Vicky9063

Custom toolbars are saved locally, in a file with the extension .xlb - that is if the toolbar is built manually. Obviously, these .xlb files will not ne on somebody elses PC...

My suggestion would be to build the toolbar on the fly - attache s piece of code similar to that shown below into the Open_Workbook event, and delete through the Close_Workbook event. This way the toolbar is only available from the appropriate workbook.

Code: 
Sub Test() 
On Error Resume Next 
Application.CommandBars("My Toolbar").Delete 
Set TB = Application.CommandBars.Add(Name:="My Toolbar") 
Set Btn = TB.Controls.Add(Type:=msoControlButton) 
With Btn 
.TooltipText = "Button1" 
.FaceId = 71 
.OnAction = "MyMacro1" 
End With 
Set Btn = TB.Controls.Add(Type:=msoControlButton) 
With Btn 
.TooltipText = "Button2" 
.FaceId = 72 
.OnAction = "MyMacro2" 
End With 
Set Btn = TB.Controls.Add(Type:=msoControlButton) 
With Btn 
.TooltipText = "Button3" 
.FaceId = 73 
.OnAction = "MyMacro3" 
End With 
Set Btn = TB.Controls.Add(Type:=msoControlButton) 
With Btn 
.TooltipText = "Button4" 
.FaceId = 74 
.OnAction = "MyMacro4" 
End With 
With Application.CommandBars("My Toolbar") 
.Visible = True 
End With 
End Sub

HTH

DominicB
 
Upvote 0
Hi Dominic,
Thanks for that it works! Do you know how i can keep the formatting on the buttons i.e. if i have made a little icon picture can it be saved in code? Also in the workbook_close() do i just put the
On Error Resume Next
Application.CommandBars("My Toolbar").Delete
cos i tried that and it doesnt seem to work..my excel vba is not great!
Thanks
V
 
Upvote 0
Hello Vicky9063,
Also in the workbook_close() do i just put the
On Error Resume Next
Application.CommandBars("My Toolbar").Delete
cos i tried that and it doesnt seem to work
Click to expand...
If you opened the workbook - which does not yet contain your toolbar - and installed both pieces of code (Workbook_Open and Workbook_Close) at the same time, you will get an error the first time you close the Wb because it's now got a Workbook_Close event trying to delete a toolbar that isn't there yet.
You should only get the error the first time the Wb gets closed and after that all should be right with the world again. (because subsequent openings will install it so it can be deleted upon closing.)

Does the Close event still error out on you after that?
 
Upvote 0
Hi Vicky9063

Do you know how i can keep the formatting on the buttons i.e. if i have made a little icon picture can it be saved in code?
Click to expand...
You need to use the PasteFace command to get involved with this. I have never actually done this, but try looking at the VBE help for PasteFace, or try a search of the board. I personally think its much easier to use the FaceID command, and there are thousands of images available.

http://www.oaltd.co.uk/MVP/Default.htm/

Download the file BtnFaces.zip from this site for a button image viewing add-in.

cos i tried that and it doesnt seem to work
Click to expand...

Something like this should work fine - don't forget to place it in the ThisWorkbook object ...

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("My Toolbar").Delete
End Sub

HTH

DominicB

EDIT : Forgot On Error statement in code snippet.
 
Upvote 0
Hi Guys,
thanks for that it is brilliant! Do i have to use the FaceID if i want to write the button in text?
Thanks!
v
 
Upvote 0
Hi Vicky9063

Not sure exactly what you're after but have an experiment with the .Style control :

Code: 
With Btn
.TooltipText = "Button1"
.FaceId = 71
.OnAction = "MyMacro1"
.Style = msoButtonIconAndCaption
End With

Also try using these values :

msoButtonAutomatic
msoButtonCaption
msoButtonIcon
msoButtonIconAndCaptionBelow
msoButtonIconAndWrapCaption
msoButtonIconAndWrapCaptionBelow
msoButtonWrapCaption

HTH

DominicB
 
Upvote 0
Hi Dominic,
Thanks for that i eventually got it to work like this:

With Btn
.TooltipText = "Show Checklist"
.Caption = "Show Checklist"
.Style = msoButtonCaption
.OnAction = "MyMacro3"
End With

Thanks!
V
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Macros for Quick Access Toolbar - Where to Save Macros
Replies
1
Views
278
6StringJazzer
6StringJazzer
AndyTampa
  • Solved
Limit Quick Tasks macro to a specific worksheet
Replies
4
Views
52
mumps
mumps
D
  • Solved
I can't assign a macro to a button.
Replies
5
Views
672
Joe4
Joe4
E
  • Question
Old XLSM files crashing Excel; looking for help to troubleshoot, please
Replies
9
Views
1K
jkpieterse
jkpieterse
T
  • Question
Call TekXL Screen Capture from user-defined macro?
Replies
1
Views
514
TheWaterbug
T

Forum statistics

Threads
1,214,983
Messages
6,122,583
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top