Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Val MultiSelect on Userform?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Val MultiSelect on Userform?
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 userformView 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Val MultiSelect on Userform?
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 "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Val MultiSelect ? | Excel Programming | |||
userform multiselect listbox problem | Excel Programming | |||
Activating userform and filling it with data form row where userform is activate | Excel Programming | |||
List Box - MultiSelect | Excel Programming | |||
MultiSelect ListBox | Excel Programming |