Use VBA to Show/Hide Built-In Toolbar

[eliz]

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

 

[Cbrine]

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]

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.