Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help.
Alot of folks have been nice to help me with things on here. I
appreciate it. I have a new project. Listbox. I have a list that I want to be accessed from a userform the list consists of just a single line of information. 1 Age 2 Education 3 Dishonorable discharge 4 Felony Conviction 5 Felony Commission all the way to #36. I'm creating a data entry form that will have name, ssn, dob, DQ reason (my listbox), date of app then obviously an enter button to enter the info onto the spreadsheet. Could somebody tell me how to make the listbox? Thanks ahead of time. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help.
Put the listbox on your userform, and add this line to your UserForm_Initialize event
Me.ListBox1.List = Worksheets("SheetName").Range("A1:A36").Value (use the actual sheet name and range where your list is stored) Then, use a CommandButton on your userform, and in its click event, use code like this to get the value into the bottom of column D: Private Sub CommandButton1_Click() Worksheets("DBSheetName").Cells(Rows.Count,4).End( xlUp)(2).Value = Me.ListBox1.Value End Sub HTH, Bernie MS Excel MVP "Morlin" wrote in message ... Alot of folks have been nice to help me with things on here. I appreciate it. I have a new project. Listbox. I have a list that I want to be accessed from a userform the list consists of just a single line of information. 1 Age 2 Education 3 Dishonorable discharge 4 Felony Conviction 5 Felony Commission all the way to #36. I'm creating a data entry form that will have name, ssn, dob, DQ reason (my listbox), date of app then obviously an enter button to enter the info onto the spreadsheet. Could somebody tell me how to make the listbox? Thanks ahead of time. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help.
The simple method of making a list box for a userform is to first create the
userform: 1. Open the VBE by pressing Alt + F11. 2. On the menu bar of the VBE, click InsertUserForm Then add the listbox to the form: 1. If the ControlToolbox toolbar is not visible, click ViewToolBox 2. Click on the ListBox icon 3. Move the cursor pointer (should be a crosshair) to a place on the UserForm that you want the ListBos to appear and left click the mouse. The ListBox should appear and the properties dialog box should appear at the lower left of the screen. If the properties box does not appear when the ListBox is added to the form, then on the menu bar select ViewProperties Window. When the properties window is visible you can add the range for your list: 1. Locate Row Source in the properties window. 2. Enter the range for the list in A1 format without quotation marks, i.e. a2:a50 or a2:z2 or if multi-column a2:d50 To open the UserForm with code, you will need to use syntax like the following UserForm1.Show To close the UserForm you would use syntax like this: Unload UserForm1 "Morlin" wrote in message ... Alot of folks have been nice to help me with things on here. I appreciate it. I have a new project. Listbox. I have a list that I want to be accessed from a userform the list consists of just a single line of information. 1 Age 2 Education 3 Dishonorable discharge 4 Felony Conviction 5 Felony Commission all the way to #36. I'm creating a data entry form that will have name, ssn, dob, DQ reason (my listbox), date of app then obviously an enter button to enter the info onto the spreadsheet. Could somebody tell me how to make the listbox? Thanks ahead of time. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help.
Hi GLWhiz,
I found your post and maybe this is what I need. How do I tell the dialog box where to post the results of my list box selections? Thanks, Orlando "JLGWhiz" wrote: The simple method of making a list box for a userform is to first create the userform: 1. Open the VBE by pressing Alt + F11. 2. On the menu bar of the VBE, click InsertUserForm Then add the listbox to the form: 1. If the ControlToolbox toolbar is not visible, click ViewToolBox 2. Click on the ListBox icon 3. Move the cursor pointer (should be a crosshair) to a place on the UserForm that you want the ListBos to appear and left click the mouse. The ListBox should appear and the properties dialog box should appear at the lower left of the screen. If the properties box does not appear when the ListBox is added to the form, then on the menu bar select ViewProperties Window. When the properties window is visible you can add the range for your list: 1. Locate Row Source in the properties window. 2. Enter the range for the list in A1 format without quotation marks, i.e. a2:a50 or a2:z2 or if multi-column a2:d50 To open the UserForm with code, you will need to use syntax like the following UserForm1.Show To close the UserForm you would use syntax like this: Unload UserForm1 "Morlin" wrote in message ... Alot of folks have been nice to help me with things on here. I appreciate it. I have a new project. Listbox. I have a list that I want to be accessed from a userform the list consists of just a single line of information. 1 Age 2 Education 3 Dishonorable discharge 4 Felony Conviction 5 Felony Commission all the way to #36. I'm creating a data entry form that will have name, ssn, dob, DQ reason (my listbox), date of app then obviously an enter button to enter the info onto the spreadsheet. Could somebody tell me how to make the listbox? Thanks ahead of time. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help.
I use the click event to do mine:
Private Sub ListBox1_Click() ActiveSheet:Range("A1") = Me.ListBox1.Value End Sub If you have a multicolumn listbox, the value is the bound column value. "ORLANDO VAZQUEZ" wrote in message ... Hi GLWhiz, I found your post and maybe this is what I need. How do I tell the dialog box where to post the results of my list box selections? Thanks, Orlando "JLGWhiz" wrote: The simple method of making a list box for a userform is to first create the userform: 1. Open the VBE by pressing Alt + F11. 2. On the menu bar of the VBE, click InsertUserForm Then add the listbox to the form: 1. If the ControlToolbox toolbar is not visible, click ViewToolBox 2. Click on the ListBox icon 3. Move the cursor pointer (should be a crosshair) to a place on the UserForm that you want the ListBos to appear and left click the mouse. The ListBox should appear and the properties dialog box should appear at the lower left of the screen. If the properties box does not appear when the ListBox is added to the form, then on the menu bar select ViewProperties Window. When the properties window is visible you can add the range for your list: 1. Locate Row Source in the properties window. 2. Enter the range for the list in A1 format without quotation marks, i.e. a2:a50 or a2:z2 or if multi-column a2:d50 To open the UserForm with code, you will need to use syntax like the following UserForm1.Show To close the UserForm you would use syntax like this: Unload UserForm1 "Morlin" wrote in message ... Alot of folks have been nice to help me with things on here. I appreciate it. I have a new project. Listbox. I have a list that I want to be accessed from a userform the list consists of just a single line of information. 1 Age 2 Education 3 Dishonorable discharge 4 Felony Conviction 5 Felony Commission all the way to #36. I'm creating a data entry form that will have name, ssn, dob, DQ reason (my listbox), date of app then obviously an enter button to enter the info onto the spreadsheet. Could somebody tell me how to make the listbox? Thanks ahead of time. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help.
This is what my code looks like:
Private Sub UserForm_Click() End Sub Private Sub CommandButton1_Click() Unload UserForm1 End Sub Private Sub ListBox1_Click() ActiveSheet: Range("Aa6:aa8") = Me.ListBox1.Value End Sub When I run this, it only takes the first item I click on and copies it down the range aa6:aa8. I want to be able to choose multiple individual items from this list box and have them show in that range. Any suggestions? My list data resides in cell c6:c8 "JLGWhiz" wrote: I use the click event to do mine: Private Sub ListBox1_Click() ActiveSheet:Range("A1") = Me.ListBox1.Value End Sub If you have a multicolumn listbox, the value is the bound column value. "ORLANDO VAZQUEZ" wrote in message ... Hi GLWhiz, I found your post and maybe this is what I need. How do I tell the dialog box where to post the results of my list box selections? Thanks, Orlando "JLGWhiz" wrote: The simple method of making a list box for a userform is to first create the userform: 1. Open the VBE by pressing Alt + F11. 2. On the menu bar of the VBE, click InsertUserForm Then add the listbox to the form: 1. If the ControlToolbox toolbar is not visible, click ViewToolBox 2. Click on the ListBox icon 3. Move the cursor pointer (should be a crosshair) to a place on the UserForm that you want the ListBos to appear and left click the mouse. The ListBox should appear and the properties dialog box should appear at the lower left of the screen. If the properties box does not appear when the ListBox is added to the form, then on the menu bar select ViewProperties Window. When the properties window is visible you can add the range for your list: 1. Locate Row Source in the properties window. 2. Enter the range for the list in A1 format without quotation marks, i.e. a2:a50 or a2:z2 or if multi-column a2:d50 To open the UserForm with code, you will need to use syntax like the following UserForm1.Show To close the UserForm you would use syntax like this: Unload UserForm1 "Morlin" wrote in message ... Alot of folks have been nice to help me with things on here. I appreciate it. I have a new project. Listbox. I have a list that I want to be accessed from a userform the list consists of just a single line of information. 1 Age 2 Education 3 Dishonorable discharge 4 Felony Conviction 5 Felony Commission all the way to #36. I'm creating a data entry form that will have name, ssn, dob, DQ reason (my listbox), date of app then obviously an enter button to enter the info onto the spreadsheet. Could somebody tell me how to make the listbox? Thanks ahead of time. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help.
You cannot use the code I gave you with a multiselect listbox. Also, you
cannot make a multiselection from a list box and transfer it to multiple cells in a range without using a loop to get the individual values of the selected items and handle them one at a time. I think this will work: Private Sub ListBox1_Click() For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then ActiveSheet.Range("A" & i + 1) _ = ListBox1.Selected(i) End If Next End Sub You might have to modify the Range parameters to meet your needs. "ORLANDO VAZQUEZ" wrote in message ... This is what my code looks like: Private Sub UserForm_Click() End Sub Private Sub CommandButton1_Click() Unload UserForm1 End Sub Private Sub ListBox1_Click() ActiveSheet: Range("Aa6:aa8") = Me.ListBox1.Value End Sub When I run this, it only takes the first item I click on and copies it down the range aa6:aa8. I want to be able to choose multiple individual items from this list box and have them show in that range. Any suggestions? My list data resides in cell c6:c8 "JLGWhiz" wrote: I use the click event to do mine: Private Sub ListBox1_Click() ActiveSheet:Range("A1") = Me.ListBox1.Value End Sub If you have a multicolumn listbox, the value is the bound column value. "ORLANDO VAZQUEZ" wrote in message ... Hi GLWhiz, I found your post and maybe this is what I need. How do I tell the dialog box where to post the results of my list box selections? Thanks, Orlando "JLGWhiz" wrote: The simple method of making a list box for a userform is to first create the userform: 1. Open the VBE by pressing Alt + F11. 2. On the menu bar of the VBE, click InsertUserForm Then add the listbox to the form: 1. If the ControlToolbox toolbar is not visible, click ViewToolBox 2. Click on the ListBox icon 3. Move the cursor pointer (should be a crosshair) to a place on the UserForm that you want the ListBos to appear and left click the mouse. The ListBox should appear and the properties dialog box should appear at the lower left of the screen. If the properties box does not appear when the ListBox is added to the form, then on the menu bar select ViewProperties Window. When the properties window is visible you can add the range for your list: 1. Locate Row Source in the properties window. 2. Enter the range for the list in A1 format without quotation marks, i.e. a2:a50 or a2:z2 or if multi-column a2:d50 To open the UserForm with code, you will need to use syntax like the following UserForm1.Show To close the UserForm you would use syntax like this: Unload UserForm1 "Morlin" wrote in message ... Alot of folks have been nice to help me with things on here. I appreciate it. I have a new project. Listbox. I have a list that I want to be accessed from a userform the list consists of just a single line of information. 1 Age 2 Education 3 Dishonorable discharge 4 Felony Conviction 5 Felony Commission all the way to #36. I'm creating a data entry form that will have name, ssn, dob, DQ reason (my listbox), date of app then obviously an enter button to enter the info onto the spreadsheet. Could somebody tell me how to make the listbox? Thanks ahead of time. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help.
After checking the code for multiselect, I realized that it would not work
as written. Here is a tested macro that does work: Private Sub UserForm_Click() x = 1 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then ActiveSheet.Range("A" & x) _ = ListBox1.List(i) End If x = x + 1 Next End Sub Note that it uses UserForm_Click instead of ListBox1_Click. That is because with multiselect the listbox click event is disabled so you can make the multiple selections. You might want to adapt the code to a command button. Otherwise, once the selection is made, just click on the form outside the listbox and the code should execute. "ORLANDO VAZQUEZ" wrote in message ... This is what my code looks like: Private Sub UserForm_Click() End Sub Private Sub CommandButton1_Click() Unload UserForm1 End Sub Private Sub ListBox1_Click() ActiveSheet: Range("Aa6:aa8") = Me.ListBox1.Value End Sub When I run this, it only takes the first item I click on and copies it down the range aa6:aa8. I want to be able to choose multiple individual items from this list box and have them show in that range. Any suggestions? My list data resides in cell c6:c8 "JLGWhiz" wrote: I use the click event to do mine: Private Sub ListBox1_Click() ActiveSheet:Range("A1") = Me.ListBox1.Value End Sub If you have a multicolumn listbox, the value is the bound column value. "ORLANDO VAZQUEZ" wrote in message ... Hi GLWhiz, I found your post and maybe this is what I need. How do I tell the dialog box where to post the results of my list box selections? Thanks, Orlando "JLGWhiz" wrote: The simple method of making a list box for a userform is to first create the userform: 1. Open the VBE by pressing Alt + F11. 2. On the menu bar of the VBE, click InsertUserForm Then add the listbox to the form: 1. If the ControlToolbox toolbar is not visible, click ViewToolBox 2. Click on the ListBox icon 3. Move the cursor pointer (should be a crosshair) to a place on the UserForm that you want the ListBos to appear and left click the mouse. The ListBox should appear and the properties dialog box should appear at the lower left of the screen. If the properties box does not appear when the ListBox is added to the form, then on the menu bar select ViewProperties Window. When the properties window is visible you can add the range for your list: 1. Locate Row Source in the properties window. 2. Enter the range for the list in A1 format without quotation marks, i.e. a2:a50 or a2:z2 or if multi-column a2:d50 To open the UserForm with code, you will need to use syntax like the following UserForm1.Show To close the UserForm you would use syntax like this: Unload UserForm1 "Morlin" wrote in message ... Alot of folks have been nice to help me with things on here. I appreciate it. I have a new project. Listbox. I have a list that I want to be accessed from a userform the list consists of just a single line of information. 1 Age 2 Education 3 Dishonorable discharge 4 Felony Conviction 5 Felony Commission all the way to #36. I'm creating a data entry form that will have name, ssn, dob, DQ reason (my listbox), date of app then obviously an enter button to enter the info onto the spreadsheet. Could somebody tell me how to make the listbox? Thanks ahead of time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
avoiding duplicates in listbox (added from another listbox) | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |