|
How do i find items selected in a list box ?
I created a listbox with "MULTI" option - but the VB help is not helping me
write the code to identify the items selected by the user. This is easy if listbox is "single". Please help thanks manda |
How do i find items selected in a list box ?
With Me.ListBox1
For i = 0 To .ListCount - 1 If .Selected(i) Then Msgbox .List(i) End If Next i End With -- HTH Bob Phillips "Listbox use in Excel" wrote in message ... I created a listbox with "MULTI" option - but the VB help is not helping me write the code to identify the items selected by the user. This is easy if listbox is "single". Please help thanks manda |
How do i find items selected in a list box ?
Hi Bob:
thank you very much for prompt help. I guess I am still new to this. When I copied your code, I am getting the error "Invalid use of Me Keyword" (as a compile error in Visual Basic under Excel 2003). I replaced the word Me by ActiveSheet and I am still getting the error. Is there a way to declare what "Me " is? thanks manda "Bob Phillips" wrote: With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then Msgbox .List(i) End If Next i End With -- HTH Bob Phillips "Listbox use in Excel" wrote in message ... I created a listbox with "MULTI" option - but the VB help is not helping me write the code to identify the items selected by the user. This is easy if listbox is "single". Please help thanks manda |
How do i find items selected in a list box ?
Me refers where the listbox is place
Worksheet Userform it is only applicable in a module associated with that location (sheet module or userform module). You don't have to use ME. YOu can replace it with a reference to that location Worksheets("Sheet1").Combobox1 Userform1.Combobox1 -- Regards, Tom Ogilvy "Listbox use in Excel" wrote in message ... Hi Bob: thank you very much for prompt help. I guess I am still new to this. When I copied your code, I am getting the error "Invalid use of Me Keyword" (as a compile error in Visual Basic under Excel 2003). I replaced the word Me by ActiveSheet and I am still getting the error. Is there a way to declare what "Me " is? thanks manda "Bob Phillips" wrote: With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then Msgbox .List(i) End If Next i End With -- HTH Bob Phillips "Listbox use in Excel" wrote in message ... I created a listbox with "MULTI" option - but the VB help is not helping me write the code to identify the items selected by the user. This is easy if listbox is "single". Please help thanks manda |
How do i find items selected in a list box ?
Hi Tom/Bob:
Thank you both for your patient responses to what might be very elementary questions. But I am not making progress and somehow there seems to be gaps in the Excel Help. Here are the details on what I am trying to accomplish: I have a spreadsheet with the first 10 even numbers in Column A (starting with 2 in A1 and ending with 20 in A10). Then I used the "Forms" toolbar to create a Listbox and used the "format control" to (a) link it to the cells A1:A10 and b) to specify that the Selection mode is "MULTI". I am trying to write a Macro to copy three items selected by the user to Cells A15-A17. I am getting stuck in (a) referencing the Listbox and then (b) retrieving the items slected by theuser Can you help me with the part : With UserForm1.Listbox1 (which give me the error " Object required") OR with Worksheets("Sheet1").Listbox1 which is giving me the error "Object does not support this method or property" Thanks manda "Tom Ogilvy" wrote: Me refers where the listbox is place Worksheet Userform it is only applicable in a module associated with that location (sheet module or userform module). You don't have to use ME. YOu can replace it with a reference to that location Worksheets("Sheet1").Combobox1 Userform1.Combobox1 -- Regards, Tom Ogilvy "Listbox use in Excel" wrote in message ... Hi Bob: thank you very much for prompt help. I guess I am still new to this. When I copied your code, I am getting the error "Invalid use of Me Keyword" (as a compile error in Visual Basic under Excel 2003). I replaced the word Me by ActiveSheet and I am still getting the error. Is there a way to declare what "Me " is? thanks manda "Bob Phillips" wrote: With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then Msgbox .List(i) End If Next i End With -- HTH Bob Phillips "Listbox use in Excel" wrote in message ... I created a listbox with "MULTI" option - but the VB help is not helping me write the code to identify the items selected by the user. This is easy if listbox is "single". Please help thanks manda |
How do i find items selected in a list box ?
The advice was for an activeX listbox. I don't recall that you specified it
was a listbox from the forms toolbar. If you assigned this macro to your listbox, it would fire when each item was clicked: Sub Listbox1_Click() Dim lbox As ListBox Dim sName As String Dim i As Long Dim sItem As String sName = Application.Caller Set lbox = ActiveSheet.ListBoxes(sName) For i = 1 To lbox.ListCount If lbox.Selected(i) Then sItem = lbox.List(i) ' do something with sItem MsgBox sItem End If Next End Sub However, you might want to assign it to a button and have your actions all at one time. If so, you would need to know the name of your listbox. (possibly something like List Box 1 then you could do Sub button1_Click() Dim lbox As ListBox Dim sName As String Dim i As Long Dim sItem As String sName = "List Box 1" Set lbox = ActiveSheet.ListBoxes(sName) For i = 1 To lbox.ListCount If lbox.Selected(i) Then sItem = lbox.List(i) ' do something with sItem MsgBox sItem End If Next End Sub -- Regards, Tom Ogilvy "Listbox use in Excel" wrote in message ... Hi Tom/Bob: Thank you both for your patient responses to what might be very elementary questions. But I am not making progress and somehow there seems to be gaps in the Excel Help. Here are the details on what I am trying to accomplish: I have a spreadsheet with the first 10 even numbers in Column A (starting with 2 in A1 and ending with 20 in A10). Then I used the "Forms" toolbar to create a Listbox and used the "format control" to (a) link it to the cells A1:A10 and b) to specify that the Selection mode is "MULTI". I am trying to write a Macro to copy three items selected by the user to Cells A15-A17. I am getting stuck in (a) referencing the Listbox and then (b) retrieving the items slected by theuser Can you help me with the part : With UserForm1.Listbox1 (which give me the error " Object required") OR with Worksheets("Sheet1").Listbox1 which is giving me the error "Object does not support this method or property" Thanks manda "Tom Ogilvy" wrote: Me refers where the listbox is place Worksheet Userform it is only applicable in a module associated with that location (sheet module or userform module). You don't have to use ME. YOu can replace it with a reference to that location Worksheets("Sheet1").Combobox1 Userform1.Combobox1 -- Regards, Tom Ogilvy "Listbox use in Excel" wrote in message ... Hi Bob: thank you very much for prompt help. I guess I am still new to this. When I copied your code, I am getting the error "Invalid use of Me Keyword" (as a compile error in Visual Basic under Excel 2003). I replaced the word Me by ActiveSheet and I am still getting the error. Is there a way to declare what "Me " is? thanks manda "Bob Phillips" wrote: With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then Msgbox .List(i) End If Next i End With -- HTH Bob Phillips "Listbox use in Excel" wrote in message ... I created a listbox with "MULTI" option - but the VB help is not helping me write the code to identify the items selected by the user. This is easy if listbox is "single". Please help thanks manda |
How do i find items selected in a list box ?
For i to 1 MILLION
THANK YOU !! next i "Tom Ogilvy" wrote: The advice was for an activeX listbox. I don't recall that you specified it was a listbox from the forms toolbar. If you assigned this macro to your listbox, it would fire when each item was clicked: Sub Listbox1_Click() Dim lbox As ListBox Dim sName As String Dim i As Long Dim sItem As String sName = Application.Caller Set lbox = ActiveSheet.ListBoxes(sName) For i = 1 To lbox.ListCount If lbox.Selected(i) Then sItem = lbox.List(i) ' do something with sItem MsgBox sItem End If Next End Sub However, you might want to assign it to a button and have your actions all at one time. If so, you would need to know the name of your listbox. (possibly something like List Box 1 then you could do Sub button1_Click() Dim lbox As ListBox Dim sName As String Dim i As Long Dim sItem As String sName = "List Box 1" Set lbox = ActiveSheet.ListBoxes(sName) For i = 1 To lbox.ListCount If lbox.Selected(i) Then sItem = lbox.List(i) ' do something with sItem MsgBox sItem End If Next End Sub -- Regards, Tom Ogilvy "Listbox use in Excel" wrote in message ... Hi Tom/Bob: Thank you both for your patient responses to what might be very elementary questions. But I am not making progress and somehow there seems to be gaps in the Excel Help. Here are the details on what I am trying to accomplish: I have a spreadsheet with the first 10 even numbers in Column A (starting with 2 in A1 and ending with 20 in A10). Then I used the "Forms" toolbar to create a Listbox and used the "format control" to (a) link it to the cells A1:A10 and b) to specify that the Selection mode is "MULTI". I am trying to write a Macro to copy three items selected by the user to Cells A15-A17. I am getting stuck in (a) referencing the Listbox and then (b) retrieving the items slected by theuser Can you help me with the part : With UserForm1.Listbox1 (which give me the error " Object required") OR with Worksheets("Sheet1").Listbox1 which is giving me the error "Object does not support this method or property" Thanks manda "Tom Ogilvy" wrote: Me refers where the listbox is place Worksheet Userform it is only applicable in a module associated with that location (sheet module or userform module). You don't have to use ME. YOu can replace it with a reference to that location Worksheets("Sheet1").Combobox1 Userform1.Combobox1 -- Regards, Tom Ogilvy "Listbox use in Excel" wrote in message ... Hi Bob: thank you very much for prompt help. I guess I am still new to this. When I copied your code, I am getting the error "Invalid use of Me Keyword" (as a compile error in Visual Basic under Excel 2003). I replaced the word Me by ActiveSheet and I am still getting the error. Is there a way to declare what "Me " is? thanks manda "Bob Phillips" wrote: With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then Msgbox .List(i) End If Next i End With -- HTH Bob Phillips "Listbox use in Excel" wrote in message ... I created a listbox with "MULTI" option - but the VB help is not helping me write the code to identify the items selected by the user. This is easy if listbox is "single". Please help thanks manda |
How do i find items selected in a list box ?
Hi Bob and Tom:
Thank you very much for your patience and help It worked and I amnow on my way manda "Tom Ogilvy" wrote: The advice was for an activeX listbox. I don't recall that you specified it was a listbox from the forms toolbar. If you assigned this macro to your listbox, it would fire when each item was clicked: Sub Listbox1_Click() Dim lbox As ListBox Dim sName As String Dim i As Long Dim sItem As String sName = Application.Caller Set lbox = ActiveSheet.ListBoxes(sName) For i = 1 To lbox.ListCount If lbox.Selected(i) Then sItem = lbox.List(i) ' do something with sItem MsgBox sItem End If Next End Sub However, you might want to assign it to a button and have your actions all at one time. If so, you would need to know the name of your listbox. (possibly something like List Box 1 then you could do Sub button1_Click() Dim lbox As ListBox Dim sName As String Dim i As Long Dim sItem As String sName = "List Box 1" Set lbox = ActiveSheet.ListBoxes(sName) For i = 1 To lbox.ListCount If lbox.Selected(i) Then sItem = lbox.List(i) ' do something with sItem MsgBox sItem End If Next End Sub -- Regards, Tom Ogilvy "Listbox use in Excel" wrote in message ... Hi Tom/Bob: Thank you both for your patient responses to what might be very elementary questions. But I am not making progress and somehow there seems to be gaps in the Excel Help. Here are the details on what I am trying to accomplish: I have a spreadsheet with the first 10 even numbers in Column A (starting with 2 in A1 and ending with 20 in A10). Then I used the "Forms" toolbar to create a Listbox and used the "format control" to (a) link it to the cells A1:A10 and b) to specify that the Selection mode is "MULTI". I am trying to write a Macro to copy three items selected by the user to Cells A15-A17. I am getting stuck in (a) referencing the Listbox and then (b) retrieving the items slected by theuser Can you help me with the part : With UserForm1.Listbox1 (which give me the error " Object required") OR with Worksheets("Sheet1").Listbox1 which is giving me the error "Object does not support this method or property" Thanks manda "Tom Ogilvy" wrote: Me refers where the listbox is place Worksheet Userform it is only applicable in a module associated with that location (sheet module or userform module). You don't have to use ME. YOu can replace it with a reference to that location Worksheets("Sheet1").Combobox1 Userform1.Combobox1 -- Regards, Tom Ogilvy "Listbox use in Excel" wrote in message ... Hi Bob: thank you very much for prompt help. I guess I am still new to this. When I copied your code, I am getting the error "Invalid use of Me Keyword" (as a compile error in Visual Basic under Excel 2003). I replaced the word Me by ActiveSheet and I am still getting the error. Is there a way to declare what "Me " is? thanks manda "Bob Phillips" wrote: With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then Msgbox .List(i) End If Next i End With -- HTH Bob Phillips "Listbox use in Excel" wrote in message ... I created a listbox with "MULTI" option - but the VB help is not helping me write the code to identify the items selected by the user. This is easy if listbox is "single". Please help thanks manda |
| All times are GMT +1. The time now is 09:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com