Solved: Simple Floating Toolbar - enhancement

[dragon576]

Hi,
I have recently tried Lucas's code for a floating toolbar http://vbaexpress.com/kb/getarticle.php?kb_id=921, and converted it to fixed, everything working fine. However rather than have this open for the entire workbook, I need to have it open for all sheets except sheet 1 ( in otherwords deactivate it for sheet 1).

I tried to move the code below to sheet 2 and changed it to a worksheet event, and added the delete code to sheet 1 as a worksheet event ), but either Excel became unstable or I keep running into a runtime 5 error with the line "oCb.Controls("Tools").Controls("myButton").Delete".
The toolbar is created when I move to sheet 2, but when I move to sheet 1 the problems start.

I have left the module code unchanged.

Original Code:

Private Sub Workbook_Open()
AddNewToolBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As Commandbar
DeleteToolbar
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("myButton").Delete
End Sub

Worksheet Code tried:
Sheet1
Private Sub worksheet_activate()
Dim oCb As Commandbar
DeleteToolbar
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("myButton").Delete
End Sub

Sheet2
Private Sub worksheet_activate()
AddNewToolBar
End Sub

Is this the right approach to get toolbars on a per sheet basis, if so what event should I use?

If not any suggestions welcome.

Thanks

Doug

[Bob Phillips]

You could just use Workbook event code

[vba]

Private Sub Workbook_Open()
AddNewToolBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar
DeleteToolbar
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("myButton").Delete
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
DeleteToolbar
If ActiveSheet.Name <> "Sheet1" Then
AddToolbar
End If
End Sub
[/vba]

[dragon576]

That worked perfectly. Thanks!!

I have also changed the toolbar so it is fixed, and displays images and text.

To have an image and text use the following in the tool bar module

Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
.FaceId = 8
.Caption = "Macro1"
.Style = msoButtonIconAndCaption
.TooltipText = "Runs Macro 1"
'the onaction line tells the button to run a certain macro
.OnAction = "Macro1"
End With

To find the Faceid for a particurlar button image use the following addin:
http://j-walk.com/ss/excel/tips/tip67.htm

Doug