HELP! How do you add another column to this code?

L

Laura L

Here is the code, all I need to do is add another column to it (columns C and
D). Any tips is greatly appreciated... been searching the net for answers
but can't figure it out.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
 
G

George Nicholson

Simplest would probably be to replace one of your If..Thens with a Select
Case;

If Target.Column = 3 Then, etc...

becomes

Select Case Target.Column
Case 3, 4
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
Case Else
'Do nothing
End Select

HTH,
 
L

Laura L

George,
I really appreciate your help. I know nothing about VBA so I've been winging
it. It's still not working for me but I think its because of how I cut and
pasted. Formatting seems off (if indeed formatting makes a difference!). Do
I still need the 4 'end ifs' at the bottom? or does the 'end select' replace
one of the 'end ifs'? Any more help is very much appreciated.

Thanks,
Laura
 
G

George Nicholson

Of the 4 original End Ifs, the 1st 2 are within the code I provided, the 3rd
was replaced by the End Select.
That should leave the 4th one remaining after the End Select, (i.e., per you
original code, there should be one End If between EndSelect and
Exithandler:)

HTH,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top