Please help,

For some reason every time I try to lock a cell based on a check box it gives me a 1004 error. Any suggestions or help would be appreciated. Code is below.

Private Sub chkCar_Click()

If chkCar.Value = False Then

Sheet1.Unprotect
Sheet1.Cells(30, 3).Interior.ColorIndex = 15
Sheet1.Cells(30, 3).Interior.Pattern = xlSolid
Sheet1.Cells(30, 3).Font.ColorIndex = 2
Sheet1.Cells(30, 3) = "LOCKED"
Sheet1.Range("C30").Locked = True
Sheet1.Protect

ElseIf chkCar.Value = True And Application.UserName = "TestUser" Then

Sheet1.Unprotect
Sheet1.Cells(30, 3).Interior.ColorIndex = 2
Sheet1.Cells(30, 3).Interior.Pattern = xlSolid
Sheet1.Range("C30").Locked = False
Sheet1.Protect

End If


End Sub

Billy

Ive just tested your code and it seems to be working fine for me,
Is there anything else which may be occuring, Also where are you obtaining the error it might give some clues to what the problem may be

Code Error happens at the following lines:
Sheet1.Range("C30").Locked = True
Sheet1.Range("C30").Locked = False

Could this be to security settings?

Billy

Bumping this up because I am still having trouble with it.

Sorry havn't been able to look at this for you yet as i have been too busy (snowed under at work) and don't have Excel at home, maybe someone else can jump in.

A few questions for you to answer though

What kind of permissions are on the sheet already, Does the code work from another workbook if so whats the difference between the 2,

Have you tried some superficial changes like changing sheet1. to something like activesheet or worksheets("Sheet1"), I tend to use the later

if you quote out the locked statement does the rest work such as the protecting

try throwing an IF statement and seeing if an error is thrown something like

if range("C30").locked = True then
msgbox "locked"
end if

this will see if you can check the locked property ratherr than trying to change it

ill see if i have time to look in the morning to see if i can help you further or if i can think of something ill post back later

David

Here is the answer for the code. Turns out I forgot to cover the other two cells in the merged cell area.

Private Sub chkCar_Click()
'If box is checked then the City/State area will fill in for the drop off and pick up with information from above.


If chkCar.Value = False Then

Sheet1.Unprotect
Sheet1.Cells(30, 3).Interior.ColorIndex = 15
Sheet1.Cells(30, 3).Interior.Pattern = xlSolid
Sheet1.Cells(30, 3).Font.ColorIndex = 2
Sheet1.Cells(30, 3) = "LOCKED"
Sheet1.Range("C30:E30").Locked = True
Sheet1.Protect

ElseIf chkCar.Value = True And Application.UserName = "WDWoodhams" Then

Sheet1.Unprotect
Sheet1.Cells(30, 3).Interior.ColorIndex = 2
Sheet1.Cells(30, 3).Interior.Pattern = xlSolid
Sheet1.Range("C30:E30").Locked = False
Sheet1.Protect
End If



End Sub

Thank you for your time,

Billy