View Full Version : Solved: Adding a New Toolbar
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
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
Works like a charm. Thanks for the help!
Opv
I will repost my followup question as a new thread since this topic has been marked "Solved."
Opv
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.