| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock different ranges after inserting data
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lock different ranges after inserting data
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lock different ranges after inserting data
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lock different ranges after inserting data
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Inserting variable outputs into different ranges | Excel Programming | |||
| Lock and protect ranges | Excel Programming | |||
| Shift the Left Boundary of Named Ranges when Inserting Columns | Excel Programming | |||
| How do I lock worksheets so only a couple ranges on each allow inp | Excel Programming | |||
| Inserting a File Name into Worksheet Ranges | Excel Programming | |||
Linear Mode
