i want the auto-open macro to make a record of the user's setting of th
toolbar (as to which toolbars have been displayed), and then remove al
toolbars and make them disengaged. i want the user not to be able to g
to ToolsOptions and change the settings there. protecting the workboo
(using a passowrd) does not prevent the user from going t
ToolsOptions.

in the auto-close macro, i want to be able to re-set the toolbars to a
they were originally as per the user's setings.

please help me with a macro code, and explanations in a novice'
language. many thanks

--
Message posted from http://www.ExcelForum.com

Here's a mcro to hide them all.

Dim aryBars

Sub toolbars()Dim i As Long

With Application
ReDim aryBars(.CommandBars.Count)
With .CommandBars("Worksheet Menu Bar")
aryBars(0) = .Enabled
.Enabled = False
End With
For i = 1 To .CommandBars.Count
aryBars(i) = .CommandBars(i).Visible
If .CommandBars(i).Visible Then
.CommandBars(i).Visible = False
End If
Next i
End With

End Sub


run in reverse at the end

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"arunjoshi " wrote in message
...
i want the auto-open macro to make a record of the user's setting of the
toolbar (as to which toolbars have been displayed), and then remove all
toolbars and make them disengaged. i want the user not to be able to go
to ToolsOptions and change the settings there. protecting the workbook
(using a passowrd) does not prevent the user from going to
ToolsOptions.

in the auto-close macro, i want to be able to re-set the toolbars to as
they were originally as per the user's setings.

please help me with a macro code, and explanations in a novice's
language. many thanks!


---
Message posted from http://www.ExcelForum.com/

many thanks. yes, the removal works. but how do i get the origina
toolbars back. i didn't quite understand what you meant by "run in th
reverse". please help

--
Message posted from http://www.ExcelForum.com

Dim aryBars

Sub toolbars()
Dim i As Long

With Application
ReDim aryBars(.CommandBars.Count)
With .CommandBars("Worksheet Menu Bar")
aryBars(0) = .Enabled
.Enabled = False
End With
For i = 1 To .CommandBars.Count
aryBars(i) = .CommandBars(i).Visible
If .CommandBars(i).Visible Then
.CommandBars(i).Visible = False
End If
Next i
End With

End Sub

Sub toolbars()
Dim i As Long

With Application
ReDim aryBars(.CommandBars.Count)
With .CommandBars("Worksheet Menu Bar")
aryBars(0) = .Enabled
.Enabled = False
End With
For i = 1 To .CommandBars.Count
aryBars(i) = .CommandBars(i).Visible
If .CommandBars(i).Visible Then
.CommandBars(i).Visible = False
End If
Next i
End With

End Sub

Sub RestoreBars()
with application.commandbars
With .CommandBars("Worksheet Menu Bar")
.Enabled = aryBars(lbound(aryBars))
End With
for i = lbound(aryBars)+1 to ubound(aryBars)
.Commandbars(i).Visible = aryBars(i)
Next
End With
End Sub

--
Regards,
Tom Ogilvy


"arunjoshi " wrote in message
...
many thanks. yes, the removal works. but how do i get the original
toolbars back. i didn't quite understand what you meant by "run in the
reverse". please help.


---
Message posted from http://www.ExcelForum.com/

tom, hi. thanks. i tried the restorebars sub but it didn't work

--
Message posted from http://www.ExcelForum.com

There was a typo in my code which I thought I had corrected, but apparently
not.

Anyway, Bob also had a little bomb in his code where he stores the
visibility of the main menu bar after he disables it, so it stores a False
for that value. I have modified Bob's code to store true for the main menu
bar.

This worked for me. Note that arybar stores the value of the menubars, so
if you hide the bars and then somehow manage to delete the values in arybar
(perhaps be hitting reset), then you will have no information to restore
from.

Dim aryBars



Sub toolbars()
Dim i As Long

With Application
ReDim aryBars(.CommandBars.Count)
With .CommandBars("Worksheet Menu Bar")
aryBars(0) = .Enabled
.Enabled = False
End With
For i = 1 To .CommandBars.Count
If i = 1 Then
aryBars(i) = True
Else
aryBars(i) = .CommandBars(i).Visible
End If
If .CommandBars(i).Visible Then
.CommandBars(i).Visible = False
End If
Next i
End With

End Sub

Sub RestoreBars()
With Application
With .CommandBars("Worksheet Menu Bar")
.Enabled = aryBars(LBound(aryBars))
End With
For i = 1 To UBound(aryBars)
If i = 1 Then
.CommandBars(i).Visible = True
ElseIf aryBars(i) = True Then
.CommandBars(i).Visible = True
End If
Next
End With
End Sub

Of course if the user has a chartsheet active when you run this you will
have problems again, but I assume you won't have that situation.

--
Regards,
Tom Ogilvy



"arunjoshi " wrote in message
...
tom, hi. thanks. i tried the restorebars sub but it didn't work.


---
Message posted from http://www.ExcelForum.com/