excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

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/

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

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/

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

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

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

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/

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

"Tom Ogilvy" wrote in message
...
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.

If you run it all in reverse, it doesn't matter


Sub toolbarsRev()
Dim i As Long

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

Tom, many thanks. The code you've posted now works just right!
I really appreciate your help

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

i am not able to unhide the toolbars

can u please send me the correct code which can hide and unhide


thank

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

The code Tom wrote last is the correct one. Here it is again ...

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 Su

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

.Enabled = aryBars(LBound(aryBars))
this code line shows error


please hel

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

that code is not working sir

there is an error
please help me get back my toolbars

thanks a ton


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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

Sub ShowBars()
With Application
.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("Worksheet Menu Bar").Visible = True
.CommandBars("Standard").Visible = True
.CommandBars("Formatting").Visible = True
.DisplayFormulaBar = True
End With
End Sub

Should get you rolling.

You have obviously destroyed the aryBar array, so that is why you get an
error.

--
Regards,
Tom Ogilvy


"vikram " wrote in message
...
that code is not working sir

there is an error
please help me get back my toolbars

thanks a ton


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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

the code is working ok on my machine. i am afraid i can't see why it'
not working with you.

i guess, tom ogilvy can help you

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

i now know how to use a macro to remove all toolbars, and then get the
back again as they were originally.

how can i do the same with the formula bar and the status bar

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

excel - modifying toolbar, keeping it unchnagebale, then re-setting it to original
 

With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With

and reverse

--

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 now know how to use a macro to remove all toolbars, and then get them
back again as they were originally.

how can i do the same with the formula bar and the status bar?


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