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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Upvote 0
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 :(
 
Upvote 0
Did you use a password? If so, you should specify it in the Protect argument.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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 ...
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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