Home |
Search |
Today's Posts |
#1
|
|||
|
|||
selecting data from a list box
I have created a list box using the multi select function. What I would like
to do is be able to highlight multiple items(not in any particular order) in the list box and then return their selection to cells M1, M2, M3 and so on. I tried writing in a macro code to do this but it didn't seem to help. I got the code from a previous posting but kep getting an error message about the ME function: Dim i As Long Dim j As Long With.Me.ListBox1 For i= 0 To .List Count -1 If.Selected(i) Then j=j + 1 me.range("A" & j).Value = .List(i) End If Next i end With End Sub Does anyone out there have any suggestions for someone new to excel? Thank you! |
#2
|
|||
|
|||
Where is your listbox?
Did you use use a listbox from the control toolbox toolbar if you put the listbox on the worksheet? How does the code get run--maybe a button from the control toolbox toolbar that's adjacent to that listbox? (Both from the control toolbox toolbar.) I put a listbox on Sheet1 and a commandbutton right next to it. I assigned the listfillrange to Sheet2!a1:a15 I double clicked on that commandbutton and pasted this code: Option Explicit Private Sub CommandButton1_Click() Dim i As Long Dim j As Long j = 0 me.range("m:m").clearcontents With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then j = j + 1 Me.Range("M" & j).Value = .List(i) End If Next i End With End Sub You had a typo in this line: With.Me.ListBox1 (no dot before the Me.listbox portion) And a typo in this portion: me.range("A" & j).Value = .List(i) You wanted column M: me.range("M" & j).Value = .List(i) You may even want to clear out column M to make sure that there's nothing left over from before. The Me refers to the thing that owns the code. In this case, it refers to the worksheet with the listbox and commandbutton. LilyDog7 wrote: I have created a list box using the multi select function. What I would like to do is be able to highlight multiple items(not in any particular order) in the list box and then return their selection to cells M1, M2, M3 and so on. I tried writing in a macro code to do this but it didn't seem to help. I got the code from a previous posting but kep getting an error message about the ME function: Dim i As Long Dim j As Long With.Me.ListBox1 For i= 0 To .List Count -1 If.Selected(i) Then j=j + 1 me.range("A" & j).Value = .List(i) End If Next i end With End Sub Does anyone out there have any suggestions for someone new to excel? Thank you! -- Dave Peterson |
#3
|
|||
|
|||
The listbox is on a worksheet and created from the control toolbox toolbar. I
was hoping that when I highlight an entry from the list box, or highlight multiple entries, that the code would automatically get run and the data would populate in cells M1,M2,M3 etc... should i still try what you suggested or is there a different means? thanks "Dave Peterson" wrote: Where is your listbox? Did you use use a listbox from the control toolbox toolbar if you put the listbox on the worksheet? How does the code get run--maybe a button from the control toolbox toolbar that's adjacent to that listbox? (Both from the control toolbox toolbar.) I put a listbox on Sheet1 and a commandbutton right next to it. I assigned the listfillrange to Sheet2!a1:a15 I double clicked on that commandbutton and pasted this code: Option Explicit Private Sub CommandButton1_Click() Dim i As Long Dim j As Long j = 0 me.range("m:m").clearcontents With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then j = j + 1 Me.Range("M" & j).Value = .List(i) End If Next i End With End Sub You had a typo in this line: With.Me.ListBox1 (no dot before the Me.listbox portion) And a typo in this portion: me.range("A" & j).Value = .List(i) You wanted column M: me.range("M" & j).Value = .List(i) You may even want to clear out column M to make sure that there's nothing left over from before. The Me refers to the thing that owns the code. In this case, it refers to the worksheet with the listbox and commandbutton. LilyDog7 wrote: I have created a list box using the multi select function. What I would like to do is be able to highlight multiple items(not in any particular order) in the list box and then return their selection to cells M1, M2, M3 and so on. I tried writing in a macro code to do this but it didn't seem to help. I got the code from a previous posting but kep getting an error message about the ME function: Dim i As Long Dim j As Long With.Me.ListBox1 For i= 0 To .List Count -1 If.Selected(i) Then j=j + 1 me.range("A" & j).Value = .List(i) End If Next i end With End Sub Does anyone out there have any suggestions for someone new to excel? Thank you! -- Dave Peterson |
#4
|
|||
|
|||
With the way I type (or point and click), I don't like to have the change event
populate the range. I'd use a button adjacent to the listbox. Then the user (me!) can double check that he or she (or I) clicked on the correct items. But if you want to try making it work when you change the listbox: Option Explicit Private Sub ListBox1_change() Dim i As Long Dim j As Long j = 0 Me.Range("m:m").ClearContents With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then j = j + 1 Me.Range("M" & j).Value = .List(i) End If Next i End With End Sub LilyDog7 wrote: The listbox is on a worksheet and created from the control toolbox toolbar. I was hoping that when I highlight an entry from the list box, or highlight multiple entries, that the code would automatically get run and the data would populate in cells M1,M2,M3 etc... should i still try what you suggested or is there a different means? thanks "Dave Peterson" wrote: Where is your listbox? Did you use use a listbox from the control toolbox toolbar if you put the listbox on the worksheet? How does the code get run--maybe a button from the control toolbox toolbar that's adjacent to that listbox? (Both from the control toolbox toolbar.) I put a listbox on Sheet1 and a commandbutton right next to it. I assigned the listfillrange to Sheet2!a1:a15 I double clicked on that commandbutton and pasted this code: Option Explicit Private Sub CommandButton1_Click() Dim i As Long Dim j As Long j = 0 me.range("m:m").clearcontents With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then j = j + 1 Me.Range("M" & j).Value = .List(i) End If Next i End With End Sub You had a typo in this line: With.Me.ListBox1 (no dot before the Me.listbox portion) And a typo in this portion: me.range("A" & j).Value = .List(i) You wanted column M: me.range("M" & j).Value = .List(i) You may even want to clear out column M to make sure that there's nothing left over from before. The Me refers to the thing that owns the code. In this case, it refers to the worksheet with the listbox and commandbutton. LilyDog7 wrote: I have created a list box using the multi select function. What I would like to do is be able to highlight multiple items(not in any particular order) in the list box and then return their selection to cells M1, M2, M3 and so on. I tried writing in a macro code to do this but it didn't seem to help. I got the code from a previous posting but kep getting an error message about the ME function: Dim i As Long Dim j As Long With.Me.ListBox1 For i= 0 To .List Count -1 If.Selected(i) Then j=j + 1 me.range("A" & j).Value = .List(i) End If Next i end With End Sub Does anyone out there have any suggestions for someone new to excel? Thank you! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expanding Data validation from List | Excel Discussion (Misc queries) | |||
How do I get LIST on the DATA menu bar-I need Create List | New Users to Excel | |||
subtotaling and manipulating a list of data | Excel Worksheet Functions | |||
subtotaling and manipulating a list of data | Excel Worksheet Functions | |||
Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions |