Data Val MultiSelect on Userform?

[TotallyConfused]

A few days ago I posted to this forum ask if it is possible to create a
list box of different items to be picked and have those items entered into a
cell. Saw this in Contextures Website. However, the sample was for an Excel
spreadsheet. I asked if it could be done in an Userform. Bernie was kind to
answer my post and provided the sample below. However, it is not working I
followed to the T and the list of items is not showing in the list box on the
Userform. Can someome please help with this.

I will need to do this 25 x. What I have on form is 20+ cities and user
will need to pick from 10 items on the list box. Same items but for each
city it can vary. Therefore I need a textbox to hold the items chose. Would
this be the best way to do this or is there some other way that is easier or
more efficient. Thank you.


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.

 

[Jacob Skaria]

Try the below steps..Hope this helps..

1. As mentioned in the earlier post design a userform and place a listbox
and set the multiselect property as specified. Place a command button in the
form.

2...User interface: You can let the user double click a highlited cell and
make the user form popup for selection. Once the Selection is made and when
the user click the comman button the selected items will be populated to a
cell. In the example below the cell is the cell to the right of the active
cell. To install the below code right click the sheet tab which you want to
work with. Right click the sheet tab and click on 'View Code'. This will
launch VBE. Paste the below code to the right blank portion. Get back to to
workbook and try out

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
'If the user form needs to be shown for a single cell
If Target.Address = "$C$1" Then Load UserForm1: _
UserForm1.Show: Cancel = True

'If the user form needs to be shown for a range of cells
If Target.Column = 3 Then Load UserForm1: _
UserForm1.Show: Cancel = True
End Sub

3.--Right click the userform>View Code and paste the below code


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

'If to be displayed in userform textbox
'(textbox control to be placed if required)
'Me.TextBox1.Text = Mid(myStr, 2)

'Populated to the right of the active cell
ActiveCell.Offset(, 1) = Mid(myStr, 2)
End Sub

Private Sub UserForm_Initialize()
'The list is populated with the 20 + items starting from
'row 1 cell A1 of sheet3...
Me.ListBox1.List = Sheets("Sheet1").Range(Range("A1"), _
Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)).Value
End Sub

If this post helps click Yes

 

[Patrick Molloy]

i changed your code a littel bit. It worked fine as it was though...

This code is the code behind the userform.
the change refelcst the selections immediately in the ttextbox. the command
button saves it to a cell (B1) on the sheet

I'd probably have had the cell next to the item in the range set to TRUE or
FALSE.


Option Explicit
Private Sub ListBox1_Change()
Build
End Sub
Private Sub UserForm_Initialize()
Me.ListBox1.List = Worksheets("Sheet1").Range("A1:A100").Value
End Sub
Sub Build()
Dim myStr As String
Dim i As Long
myStr = ""
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
myStr = myStr & ", " & Me.ListBox1.List(i)
End If
Next i
'Do something
Me.TextBox1.Text = Mid(myStr, 2)
End Sub
Private Sub CommandButton1_Click()
Build
Range("B1") = TextBox1.Text
End Sub