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

A

arunjoshi

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 Tools>Options and change the settings there. protecting the workboo
(using a passowrd) does not prevent the user from going t
Tools>Options.

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
 
B

Bob Phillips

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)
 
A

arunjoshi

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
 
T

Tom Ogilvy

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
 
T

Tom Ogilvy

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.
 
B

Bob Phillips

Tom Ogilvy said:
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.
Click to expand...

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
 
A

arunjoshi

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

vikram

i am not able to unhide the toolbars

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


thank
 
A

arunjoshi

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
 
V

vikram

that code is not working sir

there is an error
please help me get back my toolbars

thanks a ton
 
T

Tom Ogilvy

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.
 
A

arunjoshi

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
 
A

arunjoshi

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
 
B

Bob Phillips

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)
 
Top