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 =3D 1 To Application.Toolbars.Count
' Hide each toolbar.
Application.Toolbars(i).Visible =3D 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 =3D 1 To Application.Toolbars.Count
' Show each toolbar.
Application.Toolbars(i).Visible =3D 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 news:
[email protected]...
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


"Mark" <[email protected]> wrote in message
news:[email protected]...
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

Ardus Petus wrote:
>> '--------------
>> 'Declare static array
>> Dim bVisibleToolbars() As Boolean
>>=20
>> 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 =3D 1 To Application.Toolbars.Count
>> With Application.Toolbars(i)
>> 'Save state of toolbar visibility
>> bVisibleToolbars(i) =3D .Visible
>> ' Hide each toolbar.
>> .Visible =3D False
>> End With
>> ' End of loop.
>> Next i
>> End Sub
>>=20
>> ' 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 =3D 1 To UBound(bVisibleToolbars)
>> ' Restore toolbar state
>> Application.Toolbars(i).Visible =3D
>> bVisibleToolbars(i) ' End of loop.
>> Next i
>> End Sub
>> '-------------
>>=20
>> HTH
>> --
>> AP
>>=20
>> "Mark" <[email protected]> a =E9crit dans le message
>> de news: [email protected]...
>> Hi,
>>=20
>> 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.
>>=20
>> The code I am using is as follows:-
>>=20
>> Sub HideAllToolbars()
>> ' Dimension a loop variable.
>> Dim i As Integer
>> ' Loop through the total number of toolbars.
>> For i =3D 1 To Application.Toolbars.Count
>> ' Hide each toolbar.
>> Application.Toolbars(i).Visible =3D False
>> ' End of loop.
>> Next i
>> End Sub
>>=20
>> ' 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 =3D 1 To Application.Toolbars.Count
>> ' Show each toolbar.
>> Application.Toolbars(i).Visible =3D True
>> ' End of loop.
>> Next i
>> End Sub
>>=20
>> 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.=20
>>=20
>> Any ideas?
>>=20
>> Thanks,
>>=20
>> Mark