Troubleshooting Locked Cell Behavior / Tearing my hair out in frustration

[NickHosford]

Hello all! I've been a lurker for quite some time now and have always gotten great help and advice here, but I have not been able to solve this issue in my usual way.

I have a form where a user enters information into 2 cells, make a selection from a list box, and then clicks a button to populate a record on a table. This works well, but my issue is that I don't want the user to be able to change the information in the first two cells for the rest of the session (they populate the rest of the table by selecting different items from the list box and clicking the button each time) as this will make the table's calculations inaccurate.

I have tried using "Range().Locked = True", Selecting the range first and then using "Selection.Locked = True", and even a private sub that checks the Range for updates and then locks it; but for some reason I can still edit the data in those two fields after I lock them! I can't edit the data in any of the other cells on the sheet (which is as it should be) but for some reason these two cells refuse to behave!

Here is my code for the button to add the record (wherein I attempt to lock the offending cells):


If ActiveSheet.Range("O4").Value < 11 Then
Dim ChargeRow As Integer
ChargeRow = Range("O4").Value

'debugging
MsgBox Range("B7:B8").Locked

'Lock entry fields
Sheets("X-ray Calculator").Unprotect Password:="lockdown"
Range("B7:B8").Select
Selection.Interior.ColorIndex = 0
Selection.Locked = True

'debugging
MsgBox Range("B7:B8").Locked

'Insert Charge Name
Range("A" & (ChargeRow + 19)).Select
ActiveCell.Value = Sheets("X-ray search").Range("J" & (Range("O3").Value + 2))

'If deductible is not met, total charge goes to hospital, otherwise split with clinic

If Range("B" & (ChargeRow + 19)).Value > 0 Then
If Range("B" & (ChargeRow + 19)).Value > Sheets("X-ray search").Range("N" & Range("O3").Value + 2) Then
Range("C" & (ChargeRow + 19)).Select
ActiveCell.Value = Sheets("X-ray search").Range("N" & (Range("O3").Value + 2))
Else
Range("C" & (ChargeRow + 19)).Select
ActiveCell.Value = Range("B" & (ChargeRow + 19)).Value + ((Sheets("X-ray search").Range("N" & (Range("O3").Value + 2)) - Range("B" & (ChargeRow + 19)).Value) * (Range("B7").Value / 100))
End If
Else
Range("C" & (ChargeRow + 19)).Select
ActiveCell.Value = Sheets("X-ray search").Range("L" & (Range("O3").Value + 2)) * (Range("B7").Value / 100)
Range("D" & (ChargeRow + 19)).Select
ActiveCell.Value = Sheets("X-ray search").Range("M" & (Range("O3").Value + 2)) * (Range("B7").Value / 100)
End If

'Increment the Charge Row
Range("O4").Value = ChargeRow + 1

Sheets("X-ray Calculator").Protect Password:="lockdown", UserInterfaceOnly:=True
End If

End Sub

The final straw was when the message boxes I put in for debugging purposes told me the cells were correctly locked, yet I could still edit them.

Please help!

 

[NickHosford]

Sorry, everyone... I figured it out.

Apparently the "Allow users to edit Range" feature of excel overrides the Locked status of a cell.

^_^;;