PDA

View Full Version : Solved: Adding a New Toolbar



Opv
06-14-2010, 01:47 PM
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
06-14-2010, 02:24 PM
Here is an example



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


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
06-14-2010, 02:37 PM
Thanks. I'm receiving an error in the DeleteToolBar subroutine that "ToggleFormsDesign" is undefined.

Bob Phillips
06-14-2010, 03:20 PM
Picked the wrong thing from intellisense



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

Opv
06-14-2010, 03:51 PM
Works like a charm. Thanks for the help!

Opv

Opv
06-14-2010, 04:10 PM
I will repost my followup question as a new thread since this topic has been marked "Solved."

Opv