Hide and Show Toolbars

[Mark]

Hi,

I'm trying to use VBA in excel to remove all toolbars on opening a spreadsheet using auto-open and then to restore all when closing using aut-close.

The code I am using is as follows:-

Sub HideAllToolbars()
' Dimension a loop variable.
Dim i As Integer
' Loop through the total number of toolbars.
For i = 1 To Application.Toolbars.Count
' Hide each toolbar.
Application.Toolbars(i).Visible = False
' End of loop.
Next i
End Sub

' This is a sub-routine to show all of the toolbars.
Sub ShowAllToolbars()
'loop variable
Dim i As Integer
' Loop through the total number of toolbars.
For i = 1 To Application.Toolbars.Count
' Show each toolbar.
Application.Toolbars(i).Visible = True
' End of loop.
Next i
End Sub

The problem is that whilst the above works on reinstating toolbars I get far more than were originally visible. As I want to use the code on numerous machines I need to be able to restore the exact toolbars that a network user originally had.

Any ideas?

Thanks,

Mark

 

[Ardus Petus]

'--------------
'Declare static array
Dim bVisibleToolbars() As Boolean

Sub HideAllToolbars()
'Redim the bVisibleToolbars array
ReDim bVisibleToolbars(1 To Application.Toolbars.Count)
' Dimension a loop variable.
Dim i As Integer
' Loop through the total number of toolbars.
For i = 1 To Application.Toolbars.Count
With Application.Toolbars(i)
'Save state of toolbar visibility
bVisibleToolbars(i) = .Visible
' Hide each toolbar.
.Visible = False
End With
' End of loop.
Next i
End Sub

' This is a sub-routine to show all of the toolbars.
Sub ShowAllToolbars()
'loop variable
Dim i As Integer
' Loop through the total number of toolbars.
For i = 1 To UBound(bVisibleToolbars)
' Restore toolbar state
Application.Toolbars(i).Visible = bVisibleToolbars(i)
' End of loop.
Next i
End Sub
'-------------

HTH
--
AP

"Mark" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi,

I'm trying to use VBA in excel to remove all toolbars on opening a
spreadsheet using auto-open and then to restore all when closing using
aut-close.

The code I am using is as follows:-

Sub HideAllToolbars()
' Dimension a loop variable.
Dim i As Integer
' Loop through the total number of toolbars.
For i = 1 To Application.Toolbars.Count
' Hide each toolbar.
Application.Toolbars(i).Visible = False
' End of loop.
Next i
End Sub

' This is a sub-routine to show all of the toolbars.
Sub ShowAllToolbars()
'loop variable
Dim i As Integer
' Loop through the total number of toolbars.
For i = 1 To Application.Toolbars.Count
' Show each toolbar.
Application.Toolbars(i).Visible = True
' End of loop.
Next i
End Sub

The problem is that whilst the above works on reinstating toolbars I get far
more than were originally visible. As I want to use the code on numerous
machines I need to be able to restore the exact toolbars that a network user
originally had.

Any ideas?

Thanks,

Mark

 

[davesexcel]

http://support.microsoft.com/?kbid=213487

here's a code that is quite interesting,
the toggle hides and shows the original toolbars

 

[Dana DeLouis]

I'm trying to use VBA in excel to remove all toolbars

Perhaps another option if you like. I like this because it puts everything
back in its original place...

Sub FullScreen()
Application.DisplayFullScreen = True
'// Your stuff...
Application.DisplayFullScreen = False
End Sub

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


Hi,

I'm trying to use VBA in excel to remove all toolbars on opening a
spreadsheet using auto-open and then to restore all when closing using
aut-close.

The code I am using is as follows:-

Sub HideAllToolbars()
' Dimension a loop variable.
Dim i As Integer
' Loop through the total number of toolbars.
For i = 1 To Application.Toolbars.Count
' Hide each toolbar.
Application.Toolbars(i).Visible = False
' End of loop.
Next i
End Sub

' This is a sub-routine to show all of the toolbars.
Sub ShowAllToolbars()
'loop variable
Dim i As Integer
' Loop through the total number of toolbars.
For i = 1 To Application.Toolbars.Count
' Show each toolbar.
Application.Toolbars(i).Visible = True
' End of loop.
Next i
End Sub

The problem is that whilst the above works on reinstating toolbars I get far
more than were originally visible. As I want to use the code on numerous
machines I need to be able to restore the exact toolbars that a network user
originally had.

Any ideas?

Thanks,

Mark

 

[Mark]

Thanks for all replies!

Mark