Lock different ranges after inserting data

[maywood]

Hello everyone,

again, I have a question concerning programming a macro in Excel 2003.

I have 3 different rows, let's say E3:Q3, E5:Q5 and E7:Q7. If someone enters
data into one or more cells of ONE row, I want the cells in the other 2 rows
to be locked. And the data which was entered to the cells of the one row
should be copied to the other 2 rows.
Example:
Someone enters a 1, 2, 3, 4,...,12 in the cells of E3:Q3. This data should
be copied to E5:Q5 and E7:Q7 and nobody should be able to overwrite this data
in these rows. There should be message box popping up, if someone trys to
change the copied data: "you can't put your data in here...go to row 3 to
change data."

Same thing should happen if somebody starts entering data in E5:Q5 or E7:Q7.
Thanks for your help!

 

[Sam Wilson]

Hi,

This won't do the warning message but will do the rest of your request. I've
made it so that if the user deletes all values from the unlocked row, the
other two rows are then unlocked, like a reset:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo errorcatcher

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range

Set Rng1 = Range("E3:Q3")
Set Rng2 = Range("E5:Q5")
Set Rng3 = Range("E7:Q7")

Rng1.Locked = False
Rng2.Locked = False
Rng3.Locked = False

If IsEmpty(Target) Then GoTo errorcatcher

If Not Intersect(Rng1, Target) Is Nothing Then
Rng2.Value = Rng1.Value
Rng3.Value = Rng1.Value
Rng2.Locked = True
Rng3.Locked = True
End If

If Not Intersect(Rng2, Target) Is Nothing Then
Rng1.Value = Rng2.Value
Rng3.Value = Rng2.Value
Rng1.Locked = True
Rng3.Locked = True
End If

If Not Intersect(Rng3, Target) Is Nothing Then
Rng1.Value = Rng3.Value
Rng2.Value = Rng3.Value
Rng1.Locked = True
Rng2.Locked = True
End If


errorcatcher:
Application.EnableEvents = True

End Sub

 

[maywood]

Hi Sam Wilson,

your code copies the values of one range to the 2 other ranges. But it
doesn't lock the other 2 ranges...I still can change the values in all of the
cells.

 

[Sam Wilson]

Hi,

All cells are locked by default - the lock doesn't apply till you protect
the sheet.

Sam

 

[maywood]

Ah, ok!
Thanks a lot!

Hi,

All cells are locked by default - the lock doesn't apply till you protect
the sheet.

Sam