raisedwell
New Member
- Joined
- Mar 4, 2010
- Messages
- 22
I do not have much experience with VBA and I have a worksheet where I need to have a specific cell (A2) to be formatted with a data validation list to allow the user to select multiple items from the validation list and the cell should return a list of all of the selections within the cell (A2) on it's own line. I was able to locate the following VBA code to accomplish this that I have cut/pasted into the specific worksheet using the Developer button:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2")) Is Nothing 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
While this seems to accomplish the goal, when I save it as a .xlsm file and re-open it, the cell no longer allows for multiple selections and only returns a single item from the list.
Other than cut/paste the code and saving, is there something I am doing wrong? Am I missing a step?
Any help provided will be greatly appreciated!
Thanks in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2")) Is Nothing 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
While this seems to accomplish the goal, when I save it as a .xlsm file and re-open it, the cell no longer allows for multiple selections and only returns a single item from the list.
Other than cut/paste the code and saving, is there something I am doing wrong? Am I missing a step?
Any help provided will be greatly appreciated!
Thanks in advance.