Lock different ranges after inserting data
Ah, ok!
Thanks a lot!
"Sam Wilson" wrote:
Hi,
All cells are locked by default - the lock doesn't apply till you protect
the sheet.
Sam
"maywood" wrote:
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" wrote:
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" wrote:
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!
|