'Merging 2 Worksheet change events (Multiple selection drop down and changing value in dropdown to a number)
I'm trying to merge these 2 codes together. I'm very new to VBA and I can't figure out if it is even possible. I know I can't have the number change before the multiple selection. Any help with this would be great. I am trying to make it as simple as possible for a vendor to just select the item from the dropdown, but yet also easy for me to interpret when they return it. If I use only the multiple selection some of the fields could be 23 lines. If I can get the two codes to run together I would change it to have the multiple selection separated with a comma instead of populate a new line. Both of these codes work great otherwise.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rChanged As Range, c As Range
Set rChanged = Intersect(Target, Range("B2:B50, C2:C50, D2:D50, I2:I50, J2:J50"))
If Not rChanged Is Nothing Then
Application.EnableEvents = False
For Each c In rChanged
c.Value = Trim(Split(c.Value & "-", "-")(0))
Next c
Application.EnableEvents = True
End If
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 2 Or Target.Column = 3 Or Target.Column = 4 Or Target.Column = 9 Or Target.Column = 10 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Solution 1:[1]
Well I played around until I got it to work. It's not pretty and doesn't update until you make another selection in the dropdown, but it does what I was looking for. If anyone has a suggestion on making the code work a bit better I am more than willing to take constructive criticism.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 2 Or Target.Column = 3 Or Target.Column = 4 Or Target.Column
= 9 Or Target.Column = 10 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Dim rChanged As Range, c As Range
Set rChanged = Intersect(Target, Range("B2:B37, C2:C200,
D2:D200, I2:I200, J2:J200"))
If Not rChanged Is Nothing Then
Application.EnableEvents = False
For Each c In rChanged
c.Value = Trim(Split(c.Value & "-", "-")(0))
Next c
End If
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Jacob O |