|
HELP! How do you add another column to this code?
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 |
HELP! How do you add another column to this code?
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 "George Nicholson" wrote: 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, "Laura L" <Laura wrote in message ... 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 |
HELP! How do you add another column to this code?
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, "Laura L" wrote in message ... 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 "George Nicholson" wrote: 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, "Laura L" <Laura wrote in message ... 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 |
HELP! How do you add another column to this code?
Thank you for clarifying! Woo Hoo!
THANKS AGAIN! Laura "Laura L" wrote: 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 |
| All times are GMT +1. The time now is 11:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com