Use VBA to Show/Hide Built-In Toolbar

eliz

New Member
Joined
Sep 12, 2006
Messages
46
I can't seem to get this to work:

The Protection toolbar should open when the workbook file opens.
BTW, will I get an error if the toolbar is already open?

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.CommandBars("Protection").Visible = True

Dim SH As Worksheet
Dim rng As Range

On Error Resume Next

For Each SH In Worksheets
SH.Unprotect

With SH.UsedRange
.Locked = False
Set rng = Nothing
Set rng = .SpecialCells(xlCellTypeFormulas)
If Not rng Is Nothing Then
rng.Locked = True
End If
End With

SH.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingColumns:=True, AllowInsertingRows:=True

Next SH

Application.CommandBars("Protection").Visible = False
Application.ScreenUpdating = True

End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
eliz,
I'm not sure why you are trying to make the protection toolbar visible, since you then loop, and then hide it again? The user will never see in a usable form anyway? Can you explain what you are trying to do?

Cal

PS-The code for the toolbar works fine, and will not error if it is already visible.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
The code is working for me. What is the problem? Are you not seeing the toolbar? Depending on the number of sheets you have, the code could be running quickly enough that you can't tell the toolbar is open before the code ends/toolbar closes.

Why are you even showing/hiding it, anyway? You do not need it and you don't appear to be doing anything with it during the macro.

BTW, will I get an error if the toolbar is already open?
No, I don't believe so.
 

Forum statistics

Threads
1,136,613
Messages
5,676,813
Members
419,652
Latest member
jjakub33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top