Protected Worksheet: All cells except 1 need to be locked

[gingersnaps212]

I have a worksheet in which a user selects from a drop down list and from there everything else is filled out. In order to protect this and prevent users from messing up the form (thus preventing numerous headaches for me), I want to lock all cells except for this one cell containing the drop down list.

Below is the code I have:
With Sheets("Spreadsheet")
.Unprotect Password:="PSWHere"
.Cells.Locked = True
.Range("G8").Locked = False
.Protect Password:="PSWHere"
End With

But, I am getting the following error:
Run-time error 1004: Unable to set the Locked property of the Range class

I am using Excel 2003 and VB 6.5. Any help and/or suggestions would be greatly appreciated.

 

[Sahak]

Hi,

How I understand you want to lock all cells except “G8”, if so then this macro will work for you.

Sub Macro1()
    With Sheets("Spreadsheet")
        .Unprotect Password:="PSWHere"
        .Cells.Locked = True
        .Range("G8").Locked = False
        .Protect Password:="PSWHere"
    End With
End Sub

 

[gingersnaps212]

Hi,

How I understand you want to lock all cells except “G8”, if so then this macro will work for you.

Sub Macro1()
    With Sheets("Spreadsheet")
        .Unprotect Password:="PSWHere"
        .Cells.Locked = True
        .Range("G8").Locked = False
        .Protect Password:="PSWHere"
    End With
End Sub

This is exactly what I have already written as the code and I get the above mentioned 1004 error

 

[MrKowz]

Are you sure that it isn't erroring in another part of the macro? Try temporarily removing those exact lines of code and running the macro to see if it still gets the error.

 

[gingersnaps212]

Are you sure that it isn't erroring in another part of the macro? Try temporarily removing those exact lines of code and running the macro to see if it still gets the error.

I have removed the .Range("G8").Locked = False and the macro runs perfectly.

 

[Jonmo1]

Is G8 part of a Merged Cell Range?
That will cause the error.

 

[gingersnaps212]

That was it.

Changed it to read

.Range("G8:I8").Locked = False

Thanks.

 

[lenze]

Your code runs fine for me, but with all these cells "locked", unless everything else is a formula, how is the SS going to update automatically?

lenze

 

[gingersnaps212]

Your code runs fine for me, but with all these cells "locked", unless everything else is a formula, how is the SS going to update automatically?

lenze

Everything else is updated via macro that is run when a selection is clicked in that cell.

 

[lenze]

Everything else is updated via macro that is run when a selection is clicked in that cell.

And it works?? This will error out

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$G$8" Then Exit Sub
Select Case Target
Case "OK": Range("$A$1") = "OK"
Case Else:
End Select
End Sub

lenze