protect sheet userinterface only not working ?

CS6656

New Member
Joined
Jun 20, 2013
Messages
6
Hi: I have a macro that runs fine unless i protect my worksheet then i get a 1004 error .. so i tried:
============================
Private Sub Workbook_Open()
Dim Sh As Worksheet


For Each Sh In Worksheets
Sh.Protect UserInterFaceOnly:=True
Next

End Sub
===============================

no luck .... the macro still fails ....

any ideas ?

Thanks
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi and Welcome to the Board,

A few things to check...

Have you pasted this Workbook_Open procedure code into the "ThisWorkbook" Module of your workbook?
It needs to be in that code module instead of a Standard Code Module.

Are Events Enabled? In the Immediate Window of the VB Editor paste this statement then hit the Enter key:
Code:
?Application.EnableEvents
If it returns "False" on the next line then paste this statement then hit the Enter key to re-enable events.
Code:
Application.EnableEvents=True
Also, test that your Workbook_Open code is running by first unprotecting the first sheet, saving, closing and reopening the workbook.
If the first sheet is still unprotected, then the Workbook_Open code did not execute.
 

CS6656

New Member
Joined
Jun 20, 2013
Messages
6
Thanks for the response ... no Joy ...I double checked to be sure. Application.events was true and the macro does fire correctly on open event :(
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,525
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Did you use a password? If so, you should specify it in the Protect argument.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Try this test to narrow things down...

After opening your workbook and allowing your Workbook_Open, select a cell that is Locked on a Protected sheet then run this macro:

Code:
Sub TestUserInterFaceOnly()
    With Selection
        If .Locked Then
            On Error Resume Next
            .Cells(1) = "VBA changed the value of this Locked Cell"
            If Err.Number <> 0 Then
               MsgBox Err.Number & " " & Err.Description
            End If
        Else
            MsgBox "The selected cell isn't Locked. " _
                & "Choose a Locked Cell for this test"
        End If
    End With
End Sub
If you get an Error 1004 message, run this macro to reset the protection on that worksheet, then retry the Test macro.

Code:
Sub ProtectWithUserInterFaceOnlyTrue()
    ActiveSheet.Protect UserInterFaceOnly:=True

End Sub
 

CS6656

New Member
Joined
Jun 20, 2013
Messages
6
Try this test to narrow things down...

After opening your workbook and allowing your Workbook_Open, select a cell that is Locked on a Protected sheet then run this macro:

Code:
Sub TestUserInterFaceOnly()
    With Selection
        If .Locked Then
            On Error Resume Next
            .Cells(1) = "VBA changed the value of this Locked Cell"
            If Err.Number <> 0 Then
               MsgBox Err.Number & " " & Err.Description
            End If
        Else
            MsgBox "The selected cell isn't Locked. " _
                & "Choose a Locked Cell for this test"
        End If
    End With
End Sub
If you get an Error 1004 message, run this macro to reset the protection on that worksheet, then retry the Test macro.

Code:
Sub ProtectWithUserInterFaceOnlyTrue()
    ActiveSheet.Protect UserInterFaceOnly:=True

End Sub
HMM ... I tried to run the macro and it reminded me that i have 'allow cell selection' set to off .. (i couldn't select a locked cell. which is what i need for my end result)
I changed it to allow selection and the macro runs fine no error.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,584
Office Version
365
Platform
Windows
1. Can you post the code that is failing?

2. Do you have any other workbook, worksheet or standard module macros besides the failing one and the Workbook_Open codes?

Edit: I hadn't comprehended the last couple of lines of your post. Are you all set now then?
 

CS6656

New Member
Joined
Jun 20, 2013
Messages
6
Range("$D$9:$D$39").Select ' what cell range are we going to apply the validation formula to
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=GLRange ' This is the line that the debugger stops on
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
... no other code other than this routine and the workbook open one ...
I have fixed the problem by bracketing my code with unprotect/protect lines (as bleow):
=========================================================
Application.Worksheets("CA$ Expense Report").Unprotect
Range("$D$9:$D$39").Select ' what cell range are we going to apply the validation formula to
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=GLRange
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("B4").Select
Selection.Interior.ColorIndex = 35
'Selection.Interior.Color = RGB(200, 250, 200)
Application.Worksheets("CA$ Expense Report").Protect

======================================
..So .. i can make it work but not 'Cleanly' ...
it must have something to do with not allowing cell selection on the locked cells that prevents the 'userinterfaceOnly' from working properly ...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,525
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
The userinterfaceonly argument is not 100% reliable - it's generally safer to unprotect anyway.

BTW from your original post I thought it was the workbook_open code that was failing!
 

Forum statistics

Threads
1,085,075
Messages
5,381,558
Members
401,744
Latest member
Schoen

Some videos you may like

This Week's Hot Topics

Top