Data Val MultiSelect ?

[TotallyConfused]

In my quest to find examples of how to select Multiple values from drop down
list, I found this code from "Contextures" website on how to do this in Excel
spreadsheet. However, I am using a Userform and want to know how do I
incorporate this into a Userform? Can someone please help me with this? I
am new to Excel code and I apologize for my lack of knowledge. Thank you in
advance for any help you can provide.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

Dim rngDV As Range
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
If Target.Column = 3 Then
If Target.Value = "" Then GoTo exitHandler
If Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1).Value = Target.Value
Else
Target.Offset(0, 1).Value = _
Target.Offset(0, 1).Value _
& ", " & Target.Value
End If
End If
End If

exitHandler:
Application.EnableEvents = True

End Sub

 

[Bernie Deitrick]

TotallyConfused,

On your userform, add a listbox and set its multiselect property to 1- fmMultiSelectMulti, and put
the values into it like this using the userform's initital event:

Private Sub UserForm_Initialize()
Me.ListBox1.List = Worksheets("Sheet1").Range("A1:A10").Value
End Sub

(Assuming your valid list is in cells A1:A10 of Sheet1)

Also add a textbox to your ysefrom for the final string...

Add a commandbutton to your userform with code like this:

Private Sub CommandButton1_Click()
Dim i As Integer
Dim myStr As String
myStr = ""
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
If myStr = "" Then
myStr = Me.ListBox1.List(i)
Else
myStr = myStr & ", " & Me.ListBox1.List(i)
End If
End If
Next i

'Do something
Me.TextBox1.Text = myStr
End Sub

Then in code load the userform, show it, and have the user select the items from the listbox and
click the commandbutton. It will show the selected items in the text box.

HTH,
Bernie
MS Excel MVP