I've tried several ways to create and associate a toolbar button with a VBA
macro in Excel.

I'm not completely happy with any of the solutions I've come up with and
would like to know if anyone else out there would like to share their
favorite way of doing this?

The development can be as complex as is necessary.

The trick is that the resulting solution must be simple to implement but
still sturdy enough to hold up against users that can be too "creative".
Suggestions are most appreciated!

Thanks,

SB

For additions to the worksheet menu bar, 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)

surfbored wrote:

I've tried several ways to create and associate a toolbar button with a VBA
macro in Excel.

I'm not completely happy with any of the solutions I've come up with and
would like to know if anyone else out there would like to share their
favorite way of doing this?

The development can be as complex as is necessary.

The trick is that the resulting solution must be simple to implement but
still sturdy enough to hold up against users that can be too "creative".
Suggestions are most appreciated!

Thanks,

SB

--

Dave Peterson

The best way IMO is to create it on startup, and remove it on close-down of
the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Test").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Test").Delete
On Error GoTo 0

With Application.CommandBars("Worksheet Menu Bar")
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.Caption = "Test"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Test 1"
.FaceId = 169
.OnAction = "myTest1"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Test 2"
.FaceId = 170
.OnAction = "myTest2"
End With
.Visible = True
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

(replace xxxx in the email address with gmail if mailing direct)

"surfbored" wrote in message
.. .
I've tried several ways to create and associate a toolbar button with a
VBA
macro in Excel.

I'm not completely happy with any of the solutions I've come up with and
would like to know if anyone else out there would like to share their
favorite way of doing this?

The development can be as complex as is necessary.

The trick is that the resulting solution must be simple to implement but
still sturdy enough to hold up against users that can be too "creative".
Suggestions are most appreciated!

Thanks,

SB