frank_m
12-12-2010, 08:08 PM
I hope I can explain my need clearly enough.
Column 8 is for the entry of a persons name when they check out a tool. And Column 12 is for keeping a running history of every name that is ever entered into a column 8 cell.
Below is what I have so far, but as I'll described after the code it has a couple of problems.
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 8 Then
ActiveSheet.Unprotect
ActiveCell.Offset(0, 12 - ActiveCell.Column).Value = _
ActiveCell.Offset(0, 12 - ActiveCell.Column).Value _
& ", " & ActiveCell.Offset(0, 8 - ActiveCell.Column).Value
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, _
Scenarios:=True, userinterfaceonly:=True
End If
End Sub The problems I am having with this code is that when the Column 8 value is concatenated to the Column 12 Cell Value it constitutes a new change while the column 8 cell is still the active cell, and therefore a repeating looping effect. It does for some reason unknown to me stop repeating after couple hundred cycles, but that is beside the point. -- How might I code this so that it will only concatenated the value's once?
A second issue is that if I have Excel configured to move the cursor when pressing the enter key, the code will not fire. - I am aware that the entry can be completed by using the little green check mark on the formula bar which does allow my change code to fire, but I want this to be simple for the user.
Thanks for your assistance
Column 8 is for the entry of a persons name when they check out a tool. And Column 12 is for keeping a running history of every name that is ever entered into a column 8 cell.
Below is what I have so far, but as I'll described after the code it has a couple of problems.
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 8 Then
ActiveSheet.Unprotect
ActiveCell.Offset(0, 12 - ActiveCell.Column).Value = _
ActiveCell.Offset(0, 12 - ActiveCell.Column).Value _
& ", " & ActiveCell.Offset(0, 8 - ActiveCell.Column).Value
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, _
Scenarios:=True, userinterfaceonly:=True
End If
End Sub The problems I am having with this code is that when the Column 8 value is concatenated to the Column 12 Cell Value it constitutes a new change while the column 8 cell is still the active cell, and therefore a repeating looping effect. It does for some reason unknown to me stop repeating after couple hundred cycles, but that is beside the point. -- How might I code this so that it will only concatenated the value's once?
A second issue is that if I have Excel configured to move the cursor when pressing the enter key, the code will not fire. - I am aware that the entry can be completed by using the little green check mark on the formula bar which does allow my change code to fire, but I want this to be simple for the user.
Thanks for your assistance