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...

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)

"GLT" wrote in message
...
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...

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)

GLT wrote:

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...

--

Dave Peterson

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.

"Bob Phillips" wrote:

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)

"GLT" wrote in message
...
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...

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:

With Application.CommandBars("Formating")

to:

With Application.CommandBars("Formatting")


---
Regards,
Norman

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?

"Norman Jones" wrote:

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:

With Application.CommandBars("Formating")

to:

With Application.CommandBars("Formatting")


---
Regards,
Norman

GLT Wrote:
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?

"Norman Jones" wrote:

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:

With Application.CommandBars("Formating")

to:

With Application.CommandBars("Formatting")


---
Regards,
Norman


Hi GLT,

This code works fine for me. Are you putting the code into the
"WorkBook" Module?
As they are attaching the buttons to the Formatting Toolbar are you
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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=512460