Run-Time Error 1004 when attempting to lock cell in worksheet protected with UserInterface Only := True

Question

Build a new excel workbook and add the following code to an inserted module1.

In the worksheet enter:

"Test FixDate" to cell A1.
Merge and Center cells F1:H1.
Unlock Cells F1.
Enter formula in F1 = Today()

In the Formula Tab Define Name TodaysDate as $F$1:$H$1.
Rename the worksheet "Test"

In the Immediate window type Protect and press Enter.
Next type FixDate and press Enter.

A run-time error 1004 occurs on line:

rng.Locked = True

Note that the value in Range TodaysDate is changed to the text provided by the message box, but the cell properties of the range cannot be changed unless the worksheet is unprotected. I wish to change the color of the range TodaysDate to match the color of Cell A1, too. This property change also fails with run-time 1004. I have omitted the attempt from the code in building the example to be as simple as possible.

Is this an Excel bug? or have I missed some restriction about setting the range properties?

Here is the code:

Option Explicit

Global Const gPassword As String = "password"

Sub FixDate()
    Dim rng As Range
    Dim wks As Worksheet
    Set wks = Worksheets("Test")
    wks.Activate
    Set rng = Range("TodaysDate")
    If ActiveWorkbook.FileFormat <> xlOpenXMLTemplateMacroEnabled _
       And ActiveWorkbook.FileFormat <> xlOpenXMLTemplate _
        And ActiveWorkbook.FileFormat <> xlTemplate Then
        If Not rng.Locked Then
            '   Let user change the date; today's date is default
            rng.Value = InputBox("Enter competition date (mm/dd/yy), if not today.", Range("A1"), Format(Now(), "mm/dd/yy"))
            rng.Copy
            rng.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            Application.CutCopyMode = False
            rng.Locked = True
        End If
    End If
    Application.Goto Reference:=rng, Scroll:=False
    Set rng = Nothing
    Set wks = Nothing
End Sub

Public Sub ProtectWorkbook(Optional UnProtect As Boolean = False)
    '
    '   Workbook is protected (or optionally unprotected) in such a way as to allow code to change the data
    '   without unprotecting worksheets but human interface needs password.
    '
    Dim wks As Worksheet
    Dim wksActive As Worksheet
    Dim i As Integer
    Set wksActive = ActiveSheet
    For Each wks In Worksheets
        With wks
            If .Name = "Roster" Then
               On Error Resume Next
               .Visible = xlSheetHidden
            End If
            wks.UnProtect Password:=gPassword
            If UnProtect = False Then wks.Protect Password:=gPassword, UserInterfaceOnly:=True
        End With
    Next wks
    Set wks = Nothing
    wksActive.Activate
    Set wksActive = Nothing
End Sub

Public Sub Protect()
    ProtectWorkbook UnProtect:=False
End Sub

Public Sub UnProtect()
    ProtectWorkbook UnProtect:=True
End Sub

The error only occurs if, contrary to your detailed instructions, you assign the `TodaysDate` name to the range `$F$1`, as opposed to `$F$1:$H$1`. Yes, this is what happens by default in newer Excels when you select the merged cell: the name manager will pick up the address of the first cell. You need to manually correct it before saving. With `TodaysDate` being `$F$1:$H$1`, the error does not occur. — GSerg, Jul 03 '18 at 11:27

score 0 · Answer 1 · answered Jul 03 '18 at 11:43

0

Remove the offending code (rng.locked = true) and replace with Selection.Locked = True Selection.Interior.ColorIndex = wks.Range(Cells(1, 1), Cells(1, 1)).Interior.ColorIndex

This code runs OK without unprotecting the sheet. It still does not explain why the Range object rng fails. I would be interested in any comment about that.

Thanks

answered Jul 03 '18 at 11:43

Perry Sugerman

179
1
11

You [should not use `Selection`](https://stackoverflow.com/q/10714251/11683). It works for you because `Selection` of a merged cell returns a range that covers the entire merged cell (F1:H1), whereas your [incorrectly assigned name](https://stackoverflow.com/questions/51153002/run-time-error-1004-when-attempting-to-lock-cell-in-worksheet-protected-with-use#comment89290125_51153002) only covers F1. – GSerg Jul 03 '18 at 11:49

Run-Time Error 1004 when attempting to lock cell in worksheet protected with UserInterface Only := True

1 Answers1