Link to home
Create AccountLog in
Avatar of endurance
enduranceFlag for United States of America

asked on

Accessing FOrms and Controls (e.g. Listbox) from VBA

As recommended - I'm trying to stay away from using Active X .
And added a non-Active X Listbox.
How do I access the properties of the Listbox (e.g. Listcount) from VBA?


thx
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Only the ActiveX controls have properties like that.
If you are worried about ActiveX controls on a sheet, use a userform instead.
For example:
activesheet.listboxes(1).listcount

Open in new window

Avatar of endurance

ASKER

activesheet.listboxes(1).listcount
thanks I used this and it worked.
2 Q's:
(a) If activesheet.listboxes(1).name = "Listbox47" then how would I refer to the listbox using "Listbox47" instead of "1".  I tried:  activesheet.listboxes("Listbox47" ).listcount
but it returned the following error message:
Runtime error: 1004
Unable to get the Listboxes property of the Worksheet class

(b) I tried to retrieve the selections via:
For i = 0 To ActiveSheet.ListBoxes(2).ListCount - 1
    If ActiveSheet.ListBoxes(2).Selected(i) Then MsgBox (i)
Next i
returns the following error message:

Runtime error: 450
Wrong number of arguments or invalid property assignment

thx
SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
'doh - you're right - I put the spaces in, and now I can reference the listbox by name.
However, when I use the selected property - it gives an error msg.
See attached .xls

thx
Non-Active-X-Listbox.xlsm
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I did say the Selected property indexes from 1 and not 0 in my last post ;)
Well that seems fair.
endurance,
I requested from the moderator that the points be split since rorya gave you the correct answer here but you didn't use his answer properly, that's why you still had an error.