PDA

View Full Version : Solved: Excel 2003 Change Event to concatenate Column 8 value to Column 12 Value



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

Bob Phillips
12-13-2010, 01:29 AM
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Target.Column = 8 Then

Me.Unprotect

Me.Cells(Target.Row, "L").Value = _
Me.Cells(Target.Row, "L").Value & ", " & Target.Value

Me.Protect DrawingObjects:=False, Contents:=True, _
Scenarios:=True, userinterfaceonly:=True
End If

ws_exit:
Application.EnableEvents = True
End Sub

frank_m
12-13-2010, 06:21 AM
Thanks xld - Your solution is awesome :bow:
:beerchug: