Adding Customised Buttons to a toolbar

[Guest]

Hi,

I have an excel document in which I added three buttons to a toolbar which
are assigned to three different Macro's.

I then email a copy of the worksheet to my colleagues and when they open the
file, the buttons that I originally created no longer appear where I put them.

How do I ensure that any customization that I do of an Excel worksheet stays
permanently on the worksheet when it's emailed to others?

Thanks...

 

[Bob Phillips]

Build it dynamically

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application.CommandBars("Formating")

On Error Resume Next
.Controls("myButton1").Delete
.Controls("myButton2").Delete
.Controls("myButton3").Delete
On Error GoTo 0

End With

End Sub

Private Sub Workbook_Open()

With Application.CommandBars("Formating")

On Error Resume Next
.Controls("myButton1").Delete
.Controls("myButton2").Delete
.Controls("myButton3").Delete
On Error GoTo 0

With .Controls.Add(Type:=msoControlButton, temporary:=True)
.BeginGroup = True
.Caption = "myButton1"
.Style = msoButtonIcon
.FaceId = 29
.OnAction = "myMacro1"
End With

With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "myButton2"
.Style = msoButtonIcon
.FaceId = 30
.OnAction = "myMacro2"
End With

With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "myButton3"
.Style = msoButtonIcon
.FaceId = 31
.OnAction = "myMacro3"
End With

End With

End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

I think the easiest way to avoid this problem is to create the toolbar when the
workbook opens and delete it when the workbook closes.

I really like the way John Walkenbach does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Guest]

Hi Bob,

I tried your code but when I try to run it it comes up with a run-time error
'5', Invalid Procedure Call or Argument. I am using MS Excel 2002.

Can you advise what might be causing this?

Thanks,
Geoff.

 

[Norman Jones]

Hi GLT,

I tried your code but when I try to run it it comes up with a run-time
error
'5', Invalid Procedure Call or Argument. I am using MS Excel 2002.

Can you advise what might be causing this?

There is a small typo in the first line of each macro, change:

 

[Guest]

Hi,

I have fixed the Formatting, and the code executes ok (I put a couple of
Msgbox in the code), but no buttons appear when I open my document...

Can anyone advise what maybe going wrong?

 

[Desert Piranha]

Hi,

I have fixed the Formatting, and the code executes ok (I put a coupl
of
Msgbox in the code), but no buttons appear when I open my document...

Can anyone advise what maybe going wrong?

This code works fine for me. Are you putting the code into th
"WorkBook" Module?
As they are attaching the buttons to the Formatting Toolbar are yo
sure you have
enough room on your screen for all the buttons of that toolbar to show
(horizontally)
I believe you will have to save and reopen the WorkBook