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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,373
Messages
6,130,234
Members
449,567
Latest member
ashsweety

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