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
---------------
Jacob Skaria
"TotallyConfused" wrote:
> 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.
>
>
|
Similar Threads
