Different tool bars on worksheets

[dmkitz]

I would like to create a custom toolbar that is visible on one sheet of a workbook but not the other sheets. Is that possible? Thanks.

[malik641]

Hey dmkits,

You could use the Workbook_SheetActivate(ByVal Sh as Object) event to determine which sheet is in use...if it's the one with the custom toolbar...then show it...otherwise hide it.

Hope this helps

EDIT: I meant SheetActivate. (changed now)

[dmkitz]

I understand the concept but I'm not quite that VBA literate -- can you give a simple example in code? I'm working in Excel 2000. Thanks.

[malik641]

Sure

Here's an example that hides the Visual Basic toolbar when any sheet that is activated is not Sheet1...and shows it when the active sheet is Sheet1:

[VBA]Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet1" Then
Application.CommandBars("Visual Basic").Visible = True
Else
Application.CommandBars("Visual Basic").Visible = False
End If
End Sub[/VBA]

[Bob Phillips]

You would also need to check workbook_open, in case the workbook was saved with that sheet selected.

[dmkitz]

Couple questions: (1) Do I have to use "Sheet1", "Sheet2" etc. in the code or can I use the names I gave them in the sheet tabs?

(2) Where do I put the Private Sub Workbook_... code?

(3) I'm afraid I don't understand xld's advice about checking workbook_open.

I put the code in the "This Workbook" object and nothing happened, so I tried it in the "Sheet1" object and still nothing.

Thanks for your patience.

[dmkitz]

I think I've got it . . . still testing
dmk

[dmkitz]

Okay, I just need a tiny bit more help, please! (1) When I close Excel and reopen, the custom toolbar that was last open is now open in my normal template and I don't want it to be. (2) When I first open the workbook, neither of the custom toolbars is visible in the first sheet, but after I move to a different sheet, then the expected custom toolbar is present.

Here's my code. (The commented code didn't seem to make any difference but I didn't want to blow it away, just in case.)

[VBA]
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Financial Statement" Then
Application.CommandBars("DCI FDS").Visible = True
Application.CommandBars("DCI MES").Visible = False
'Else
' Application.CommandBars("DCI FDS").Visible = False
' Application.CommandBars("DCI MES").Visible = True
End If
If Sh.Name = "MES" Then
Application.CommandBars("DCI FDS").Visible = False
Application.CommandBars("DCI MES").Visible = True
'Else
' Application.CommandBars("DCI FDS").Visible = True
' Application.CommandBars("dci mes").Visible = False
End If

If Sh.Name = "SalaryCalc" Then
Application.CommandBars("DCI FDS").Visible = False
Application.CommandBars("DCI MES").Visible = False
End If
If Sh.Name = "SalaryCalcAVERAGE" Then
Application.CommandBars("DCI FDS").Visible = False
Application.CommandBars("DCI MES").Visible = False
End If
If Sh.Name = "READ ME" Then
Application.CommandBars("DCI FDS").Visible = False
Application.CommandBars("DCI MES").Visible = False
End If
End Sub
[/VBA]
Your advice please?
Diane

[malik641]

What xld was talking about was to take a look into the Workbook_Open() event. When the workbook opens this event is triggered and you can find out which is the activesheet and decide to hide or show whatever toolbars you want to.

By the way, I formatted your code a bit...it should work the same, though not tested:

[VBA]Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Financial Statement" Then
Application.CommandBars("DCI FDS").Visible = True
Application.CommandBars("DCI MES").Visible = False

ElseIf Sh.Name = "MES" Then
Application.CommandBars("DCI FDS").Visible = False
Application.CommandBars("DCI MES").Visible = True

ElseIf Sh.Name = "SalaryCalc" Or "SalaryCalcAVERAGE" Or "READ ME" Then
Application.CommandBars("DCI FDS").Visible = False
Application.CommandBars("DCI MES").Visible = False

End If
End Sub[/VBA]Good job so far, btw

[dmkitz]

Thanks. Okay, just one thing left: When I open the workbook, neither of the custom toolbars is visible (even though one of the worksheets where one should be visible is open). I thought that putting this code in the Workbook would do that, but it tells me I'm missing an object.
[VBA]
Private Sub Workbook_Open()
If Sh.Name = "Financial Statement" Then
Application.CommandBars("DCI FDS").Visible = True
ElseIf Sh.Name = "MES" Then
Application.CommandBars("DCI MES").Visible = True
End If

End Sub

[/VBA]
It all seems so logical -- but why doesn't it work? Thanks.
Diane

[Bob Phillips]

Sh is not defined, so you have to use activesheet.

Shouldn't you also make sure that they don't show otherwise?

[vba]

Private Sub Workbook_Open()
With Application
.CommandBars("DCI FDS").Visible = ActiveSheet.Name = "Financial Statement"
.CommandBars("DCI MES").Visible = ActiveSheet.Name = "MES"
End With
End Sub
[/vba]

[dmkitz]

Thank you all so much for your help. Everything is working well. (I did use the "before close" to turn off the custom toolbars.)
Diane

[Ken Puls]

Joseph and Bob,

Just curious here... why use the Workbook_SheetActivate to test the name of the sheet and act accordingly, and also check the Workbook_Open?

To me, this seems like a perfect place to use the Worksheet_Activate and Workbsheet_Deactivate events to load/unload the toolbars...

[malik641]

Ken,

I would use Workbook_Open because the Workbook_SheetActivate/SheetDeactivate and Worksheet_Activate/Deactivate events are not triggered when the workbook is opened. So if someone manually closed the custom toolbar(s) on the sheet that it's supposed to be turned on, then saves the workbook on that page and closes, then it won't be there when reopened unless they go to another sheet then back again. Which is why I think the Workbook_Open event should be used. It's small, and probably wouldn't happen often....but it still could happen. And for me, it would bother me to know that it could happen...so I would definitely have that code there

As far as Worksheet_Activate/Deactivate goes...I guess it would be better to have the code there for turning on and off the toolbars...because if they delete the sheet with the toolbars, then the code goes with it (but it still triggers the Worksheet_Deactivate event once deleted, effectively hiding the toolbars that need be) and save some memory (not much if that's the only code though...).

[Ken Puls]

Worksheet_Activate/Deactivate events are not triggered when the workbook is opened.

Fair enough, Joseph, good point.

[malik641]

Fair enough, Joseph, good point.

[dmkitz]

When I open the workbook on another user's computer, I get a runtime error and when I choose to debug, the name of the custom toolbar is highlighted in the code. I saved the workbook as a template, thinking the custom toolbars would be present no matter what machine the workbook is opened on, but is that not true? (This is Excel 2000)