listbox

[Guest]

hi,

I hae the following in an excel spreadsheet rathr than a userform:

For x = 0 to testbox.ListCount - 1

' If the item is selected...
If testbox.Selected(x) = True Then

'...display the Selected item.
MsgBox testbox.List(x)

End If

Next x

but every time i run the code, i get an "object required" error message.

why would this be happening?

thanks in advance,
geebee

 

[stevebriz]

is the code in sheet1? and is the listbox called testbox?

 

[Guest]

I assume you mean a ListBox. The following is for an ActiveX ListBox.
However, your code does not make a lot of sense. You can simply use the
following line to obtain the selected value:-

MsgBox Sheets("Sheet1").ListBox1.Value

Following is your code edited:-

For x = 0 To Sheets("Sheet1").ListBox1.ListCount - 1

' If the item is selected...
If Sheets("Sheet1").ListBox1.Selected(x) = True Then

'...display the Selected item.
MsgBox Sheets("Sheet1").ListBox1.List(x)

End If

Next x

Regards,

OssieMac

 

[Guest]

I just realised that your method is correct if you have a multi select listbox.

Regards,

OssieMac

 

[Guest]

hi,

i made sure my listbox is multiselect. it is in a sheet caled "tester"
instead of a userform.
i keep getting an "object required" error message. her is what i have so far:

Dim x as Long
For x = 0 to listbox4.ListCount - 1
If listbox4.Selected(x) = True Then
MsgBox listbox4.List(x)
End If
Next x

can someone tell me what I am doing wrong?


thanks in advance,
geebee

 

[Guest]

Hi again,

My first reply yesterday gave you a copy of your code with the required
corrections. The missing object is your worksheet. Here is your code again
with 2 options as to how it can be corrected:-

Example 1:

Dim x As Long

For x = 0 To Sheets("Tester").ListBox4.ListCount - 1
If Sheets("Tester").ListBox4.Selected(x) = True Then
MsgBox Sheets("Tester").ListBox4.List(x)
End If
Next x

Example 2: (Method preferred by professionals but the previous one works
well.)

Dim x As Long

With Sheets("Tester")
For x = 0 To .ListBox4.ListCount - 1
If .ListBox4.Selected(x) = True Then
MsgBox .ListBox4.List(x)
End If
Next x
End With


Regards,

OssieMac

 

[Guest]

hi,

thanks for your patience, and i am sorry if i seem like an idiot, but i now
have the following code:

Dim x As Long

With Sheets("tester")
For x = 0 To .ListBox13.ListCount - 1
If .ListBox13.Selected(x) = True Then
MsgBox .ListBox13.List(x)
End If
Next x
End With

and now i am geting an error message like "run-time error 438: object doesnt
support this property or method"

does it have something to do with the fact that listbox13 may not be named
as such? how do i get the name of the listbox located in the worksheet? i
am not sure what is going on here.

thanks in advance,
geebee

 

[Guest]

I'm sure you are not an idiot; just a newbee to VBA.

O.K. there are 2 types of controls. One type is the Forms Controls and the
other is ActiveX controls. Both are almost identical in their purpose but
they operate differently. ActiveX controls are the later technology and I
think that the Forms controls are only there to keep xl backward compatible.
I think that ActiveX controls started with xl2000 but I’ll stand correcting
on that but they have certainly been around since xl2002.

In xl 2007 you will see both types in the drop down when you click on Insert
Controls.

In earlier versions of xl, to see the Forms controls, you need the Forms
tool bar visible. To see the ActiveX controls you need the Control Toolbox
tool bar visible.

I just tested your code and it appears that you have created your List Box
from the Forms tool bar and of course the code is for ActiveX controls and
does not work on on Forms Controls. There are a number of advantages to using
the ActiveX controls so rather than alter the code to work with Forms
controls I’ll try to explain how to create an ActiveX control and set its’
properties.

Click on the List box icon and then on the worksheet and drag the list box
out to the required size. (Same as for the Forms controls). Note the icon on
the toolbar that looks like a blue set square, pencil and ruler. It is called
Design Mode. It automatically turns on when you create the list box but more
on Design Mode later.

Right click on the list box after you have set its’ size (Note Design Mode
needs to be on) and then select Properties. The top of the list is the list
box name which is user changeable. It is this name which you use to refer to
the control throughout your VBA code. Check out the other properties
including Multi select.

Before you can use the list box, turn off Design Mode. (Click on the icon.).
Now at any time you want to make changes to the properties, you need to turn
design mode on.

Now turn design mode on and this time when you right click, select View
Code. The VBA editor will open and place a private sub name and end sub. Note
in the Project explorer that the code is attached to the Sheet name on which
the list box has been inserted. Code that you want to run when the user
selects a value in the list box is inserted here. However, it does not
operate with multi list boxes because it would not know when the user has
finished selecting. The code you have for multi select can be in a Module.

Try the above and get back to me if you have any further questions.

Also what version of xl you are using? (It is a good idea to always include
it on requests to this forum.)

Regards,

OssieMac