Tim F-W . . . Toolbar hiding ALMOST there
Posted by JAF on January 18, 2001 1:26 AM
Hi Tim
Thanks for your help so far on hiding and restoring toolbars. The slight amendment to the code you posted yesterday works fine, apart from one small thing...
The toolbars that I have open are Standard, Formatting, Control Toolbox, Drawing and a custom toolbar that I created. They all hide and restore EXCEPT for the Control Toolbox toolbar.
On Sheet1, I have the label "Toolbars" in cell A1 and a named Range (TBListRange) which is defined as Cell A2 (I have tried setting this named range to cover various numbers of cells in Column A, but this doesn't seem to make any difference).
When I run the ClearTBs and ReturnTBs code, the following cells are populated on Sheet1:
A2 - 1
A3 - 2
A4 - 5
A5 - 15
I presume that these are the numbers which identify to Excel which toolbars are open.
Any suggestions as to why it's excluding the Control Toolbox from the list?
JAF
Posted by JAF on January 18, 2001 1:52 AM
Update...
I've just tested the ClearTBs and ReturnTBs code with all toolbars.
The code hides and unhides Standard(1), Formatting(2), Pivot Table(3), Chart(4), Drawing(5), Forms(7) and Visual Basic(9).
It doesn't hide/unhide Reviewing, Control Toolbox, Clipboard, External Data, Picture, Web or Word Art.
Just thought you'd like to know!
JAF
Posted by Dave Hawley on January 18, 2001 2:12 AM
Hi JAF
I never saw the code you are refering to but this code of mine maybe of some use to you. It creates a list of all visible Command bars on Activation of the Workbook. The list is created in Column 1 of Sheet1 (code name) which is never visible.
On deactivation of the Workbook all Command bars are restored.
Option Explicit
Dim Tbars As CommandBar, i As Integer
Dim FBar As Boolean
Private Sub Workbook_Activate()
Application.ScreenUpdating = False
FBar = False
On Error Resume Next
With Sheet1
.Visible = xlSheetVeryHidden
.Columns(1).Clear
i = 0
For Each Tbars In Application.CommandBars
If Tbars.Visible = True Then
i = i + 1
.Cells(i, 1) = Tbars.Name
Tbars.Visible = False
End If
Next
End With
Application.CommandBars("Worksheet Menu Bar").Enabled = False
If Application.DisplayFormulaBar = True Then
Application.DisplayFormulaBar = False
FBar = True
End If
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Deactivate()
Dim BarName As String
i = 0
On Error Resume Next
With Sheet1
For i = 1 To .Cells(65536, 1).End(xlUp).Row
BarName = .Cells(i, 1)
Application.CommandBars(BarName).Visible = True
Next
End With
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFormulaBar = FBar
End Sub
Hope I'm on the right track.
- OzGrid Business Applications
Posted by Dave Hawley on January 18, 2001 2:12 AM
Hi JAF
I never saw the code you are refering to but this code of mine maybe of some use to you. It creates a list of all visible Command bars on Activation of the Workbook. The list is created in Column 1 of Sheet1 (code name) which is never visible.
On deactivation of the Workbook all Command bars are restored.
Option Explicit
Dim Tbars As CommandBar, i As Integer
Dim FBar As Boolean
Private Sub Workbook_Activate()
Application.ScreenUpdating = False
FBar = False
On Error Resume Next
With Sheet1
.Visible = xlSheetVeryHidden
.Columns(1).Clear
i = 0
For Each Tbars In Application.CommandBars
If Tbars.Visible = True Then
i = i + 1
.Cells(i, 1) = Tbars.Name
Tbars.Visible = False
End If
Next
End With
Application.CommandBars("Worksheet Menu Bar").Enabled = False
If Application.DisplayFormulaBar = True Then
Application.DisplayFormulaBar = False
FBar = True
End If
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Deactivate()
Dim BarName As String
i = 0
On Error Resume Next
With Sheet1
For i = 1 To .Cells(65536, 1).End(xlUp).Row
BarName = .Cells(i, 1)
Application.CommandBars(BarName).Visible = True
Next
End With
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFormulaBar = FBar
End Sub
Hope I'm on the right track.
Dave
- OzGrid Business Applications
Posted by JAF on January 18, 2001 2:48 AM
Thanks - Problem Solved!
That works nicely.
Thanks for your help.
JAF
