The Cell Link is ignored for a MultiSelect List Box. You have to
access the selected items in code. Test the Selected property of each
item in the List to determine which items are selected.
Private Sub ListChoices()
Dim i As Integer
Dim cnt As Integer
' Get the number of items in the list
cnt = ListBox1.ListCount
' Loop through the items, checking each one
' to see if it is selected
For i = 0 To (cnt - 1)
If ListBox1.Selected(i) = True Then
Debug.Print ListBox1.List(i)
End If
Next i
End Sub
Additional Resources:
Look at Excel VBA Help for the ActiveX ListBox Object
- Hammer |
Request for Answer Clarification by
davidparks21-ga
on
18 Mar 2003 06:45 PST
I've tried this but only get an error "Object required"
All I need is a way to refference the list box object from VBA. Where
are you comming up with the object "ListBox1"?
I've used this code:
Sub test1()
x = MetricLB.ListCount
MsgBox x
End Sub
MitricLB is the name of the list box in the excel sheet. All I need
to know is how to refference this list box from VBA. The rest of the
methods I can figure out on my own.
Thanks!
David
|
Request for Answer Clarification by
davidparks21-ga
on
18 Mar 2003 06:54 PST
One other note. I think your example is assuming the listbox is in a
form (I notice a similar example in the documentation which is
refferencing a listbox in a form).
It is not. The list box I'm working with is in the workbook, there is
no form. This is where I can't refference the listbox from.
Thanks,
David
|
Clarification of Answer by
hammer-ga
on
18 Mar 2003 07:03 PST
You are referencing the list box correctly, assuming that MetricLB is
actually the name of your list box. Check the properties of the list
box and confirm that you are spelling the name correctly.
I got ListBox1 because I dropped a list box on a spreadsheet to test
this for you and mine was named ListBox1.
In the VBA Editor for your worksheet, there are two dropdowns at the
top of the code window. The one on the left lists the objects on your
worksheet. The first choice is (General). Drop down this list and look
for the name of your list box.
If, after confirming the object name, you are still unable to
reference the list box, I will need to see your Excel spreadsheet to
help you troubleshoot. If necessary, can you post it online where I
can download it?
- Hammer
|
Clarification of Answer by
hammer-ga
on
18 Mar 2003 07:06 PST
How did you create your ListBox? I got mine by using the Control Box Toolbar.
- Hammer
|
Request for Answer Clarification by
davidparks21-ga
on
18 Mar 2003 07:22 PST
The naming of my list box must be what's wrong.
I created the listbox with the Forms toolbar (view->toolbars->forms).
When I right click on the listbox there is a name in the upper left of
excel (this normally shows the cell refference you currently have
selected). That is what I changed to MetricLB.
When I created a new list box, it was called "List Box 9" I not the
spaces. This makes me think that this is the wrong refference to the
object.
How can I find what the correct refference to the object is.
In the VBA editor, I know of the two pulldown lists you reffer to, but
these only list "(General)" and "Worksheet" under any of the
worksheets, and just "(General)" under any module, for the left
pulldown list. If I select worksheet it only shows the events for that
worksheet.
Thanks,
David
p.s. I will increase the value of this question to $4 assuming we
figure out what I'm doing wrong here as soon as I figure out how, I
think I have to finalize it to do that.
|
Clarification of Answer by
hammer-ga
on
18 Mar 2003 07:37 PST
Typing in the dropdown you mention will have no effect. Excel will try
to select an existing object called MetricLB, and, of course, there
isn't one.
To create a ListBox:
1. From the View Menu, select Toolbars/Control Toolbox. The Control
Toolbox should appear.
2. In the Control Toolbox, click on the ListBox button.
3. Left-click and drag on your worksheet to draw the ListBox. When you
release the mouse button, you should have a ListBox.
4. Right-click on the ListBox and choose Properties.
5. The first Property should be (Name). This tells you the name of
your ListBox. You can change the name by typing over it and saving
your worksheet. You can also change the MultiSelect setting from here.
6. Make your changes, close the Property Editor and save your
worksheet.
7. From the Tools Menu, select Macro/Visual Basic Editor.
8. The tree in the left panel of the VBA Editor should be open on the
last branch. The choices should show something like Sheet1(Sheet1) and
This Workbook. If you have named your worksheets, they will appear by
name. Double-click on your worksheet. NOT "This Workbook". Your
listbox should appear in the dropdown object list along with (General)
and Worksheet.
- Hammer
|
,
0 Comments
)