dragon576
09-10-2007, 10:11 AM
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
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