Trouble locking cells....

[AshN]

I am trying to set up an excel workbook that will run something like a time clock. I want certain cells to be locked to all without a password and certain cells to lock once a person has entered data into them. These will all be on the same sheet. I've gotten each to work without the other turned on but as soon as I try to make it all work at the same time it freaks out (i.e. the cells that are supposed to lock after data entry start locking everytime the worksheet is protected, or ask for a password to enter data, etc) It seems like the info I'm using is misreading somewhere. I'm very new to using VBA so I'm having trouble trouble-shooting it. Here's a copy of the VBA I'm using to lock the cells after data entry. If you need more information please let me know. Any help anyone can give would be awesome! Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("H4:H30")) Is Nothing Then

If Target.Locked = False Then
ActiveSheet.Unprotect "1234"
Target.Locked = True
ActiveSheet.Protect "1234"
End If
End If
End Sub

 

[Muhamed Faizal]

I haven't tested the code, by looking I feel you missed 'else'

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H4:H30")) Is Nothing Then
If Target.Locked = False Then
ActiveSheet.Unprotect "1234"
Else
Target.Locked = True
ActiveSheet.Protect "1234"
End If
End If
End Sub

 

[AshN]

I was missing the "else" part but it didn't change anything...now it's unlocking the whole worksheet every time I enter data into the column I want to lock after the data is entered. It's also leaving certain cells in that same column that have no data locked.

 

[andrewkard]

Hello, maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:AA100")
If Not Intersect(r, Range("H4:H30")) Is Nothing Then
If r.Locked = False Then
ActiveSheet.Unprotect "1234"
Else
r.Locked = True
ActiveSheet.Protect "1234"
End If
End If
End Sub

 

[AshN]

Is it possible for me to have some of the cells locked and some set to lock after the data is entered? Do they have to be formatted a certain way in order to have everything read correctly?

 

[andrewkard]

Is it possible for me to have some of the cells locked and some set to lock after the data is entered? Do they have to be formatted a certain way in order to have everything read correctly?

Do you want that range (except H1:H30) was locked and since enter data in H1:H30 will lock all sheet?

 

[AshN]

--Do you want that range (except H1:H30) was locked and since enter data in H1:H30 will lock all sheet?

I specifically want cells A-G locked no matter what and then cell H to lock once data has been entered into it.

 

[andrewkard]

Try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H4:H30")) Is Nothing Then
Else
ActiveSheet.Unprotect "1234"
Range("H4:H30").Locked = True
ActiveSheet.Protect "1234"
End If
End Sub