abesacto916
New Member
- Joined
- Mar 6, 2016
- Messages
- 2
Hey all,
I'm trying to teach myself VBA and this forum has been extremely useful. I've been using the code below, which I found online. The only issue I have is applying the code to from B2:B100. I juts cannot seem to have it apply to all the cells and I'm lost. I created a Data Validation List Box and this code allows me to select multiple choices, but I need this to occur from B2:B100.
Help!!!!!
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from Welcome to Trump Excel » Trump Excel
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$B$3" 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
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
I'm trying to teach myself VBA and this forum has been extremely useful. I've been using the code below, which I found online. The only issue I have is applying the code to from B2:B100. I juts cannot seem to have it apply to all the cells and I'm lost. I created a Data Validation List Box and this code allows me to select multiple choices, but I need this to occur from B2:B100.
Help!!!!!
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from Welcome to Trump Excel » Trump Excel
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$B$3" 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
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub