Solved: Adding a New Toolbar

[Opv]

Is there anyway to create a new toolbar and have it associated and linked to a specific worksheet so that when a worksheet is sent via email to someone adn that person opens the worksheet, the toolbar is available? I don't want/need it to be available for other worksheets as all of the macros linked to the buttons apply only to one specific worksheet.

I tried adding buttons to the actual worksheet but it has become rather cluttered with buttons. Looking for a better solution.

Opv

[Bob Phillips]

Here is an example

[vba]

Option Explicit

Const TOLBAR_NAME As String = "MyToolbar"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteToolbar
End Sub

Private Sub Workbook_Open()
Call DeleteToolbar

Call CreateToolbar
End Sub

Private Sub CreateToolbar()

With Application.CommandBars.Add(Name:=ToggleFormsDesign, temporary:=True)

With .Controls.Add(Type:=msoControlButton)

.Caption = "Button1"
.FaceId = 29
.OnAction = "procButton1"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "Button2"
.FaceId = 30
.OnAction = "procButton2"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "Button3"
.FaceId = 31
.OnAction = "procButton3"
End With

.Position = msoBarTop
.Visible = True
End With
End Sub

Private Sub DeleteToolbar()
On Error Resume Next
Application.CommandBars(ToggleFormsDesign).Delete
End Sub
[/vba]

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

[Opv]

Thanks. I'm receiving an error in the DeleteToolBar subroutine that "ToggleFormsDesign" is undefined.

[Bob Phillips]

Picked the wrong thing from intellisense

[vba]

Option Explicit

Const TOLBAR_NAME As String = "MyToolbar"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteToolbar
End Sub

Private Sub Workbook_Open()
Call DeleteToolbar

Call CreateToolbar
End Sub

Private Sub CreateToolbar()

With Application.CommandBars.Add(Name:=TOLBAR_NAME, temporary:=True)

With .Controls.Add(Type:=msoControlButton)

.Caption = "Button1"
.FaceId = 29
.OnAction = "procButton1"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "Button2"
.FaceId = 30
.OnAction = "procButton2"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "Button3"
.FaceId = 31
.OnAction = "procButton3"
End With

.Position = msoBarTop
.Visible = True
End With
End Sub

Private Sub DeleteToolbar()
On Error Resume Next
Application.CommandBars(TOLBAR_NAME).Delete
End Sub
[/vba]

[Opv]

Works like a charm. Thanks for the help!

Opv

[Opv]

I will repost my followup question as a new thread since this topic has been marked "Solved."

Opv