Hiding then Showing Different Toolbars

Posted by JAF on January 10, 2001 4:31 AM

Hiya

I have a spreadsheet which is updated by several different users.

The spreadsheet has Workbook_Open code that removes the Standard and Formatting toolbars and Workbook_BeforeClose code that displays them again.

I want to be able to hide ALL toolbars that a user may have open and then reinstate those same toolbars in the BeforeClose code. The problem that I have is that different users have different toolbars enabled.

Example:
User 1 has Standard, Formatting and Drawing toolbars enabled.
User 2 has Standard, Formatting and Forms toolbars enabled.
User 3 has Standard, Formatting, Drawing, Web and Custom toolbars enabled.

Is there any way for Excel to note which toolbars are displayed when the file is opened, close those toolbars and then reinstate them in the BeforeClose code?

In other words, when using the file no toolbars would be visible, but when the file is closed, each users "unique" toolbars would be restored.

Any suggestions?

JAF

Posted by Tim Francis-Wright on January 10, 2001 8:38 AM

Here's something that does the trick for me;
I have an application that runs ClearTBs as
part of an Auto_Open routine, and ReturnTBs
as part of an Auto_Close routine.

Sheet1 is a hidden sheet that holds all sorts
of good things. A1 is a label ("Toolbars");
A2 and so on are reserved for toolbar ID #s.

Sub ClearTBs()
Dim CurWS As Object, List As Object
Dim ListNum As Integer
Dim TBCount As Integer
Dim i As Integer, j As Integer, vTBID As Integer
Dim vTBListRange As Object

Set CurWS = ThisWorkbook.Sheets("Sheet1")
Set List = Application.Toolbars
TBCount = Application.CountA(CurWS.Range("A:A"))
If TBCount > 1 Then
Set vTBListRange = CurWS.Range("$A$2:$A$" & TBCount)
Application.Calculation = xlCalculationManual
vTBListRange.ClearContents
End If
ListNum = List.Count
j = -1
For i = 1 To ListNum
If List(i).Visible = True Then
j = j + 1
vTBID = i
CurWS.Range("TBListRange").Offset(j).Value = vTBID
End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Sub ReturnTBs()
Dim TBCount As Integer
Dim CurWS As Object, Item As Object, vTBListRange As Object

On Error Resume Next
Set CurWS = ThisWorkbook.Sheets("Sheet1")
TBCount = Application.CountA(CurWS.Range("A:A"))
If TBCount = 1 Then Exit Sub
Set vTBListRange = CurWS.Range("$A$2:$A$" & TBCount)
For Each Item In vTBListRange
Application.Toolbars(Item).Visible = True
Next Item
End Sub