Please help, i'm new to excel and it's a maze - List box

bibstar03

New Member
Joined
Jan 30, 2003
Messages
25
i have created a userform in vb which links to sources on a worksheet, if i have a normal text box i can change the data on either worksheet or userform and the data changes on both. on the worksheet i have a drop down list for one piece of info, created using validation, how do i get the list to appear on my user form. i have created combo and list boxes and tried what i thought would work, which was changing the controlsource to the name of the cell which the list appears in on the worksheet but the combo box doesnt show all the value of the box???????????

thanks

if that doesnt make any sense please tell me lol
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Say for example that the cells you are using in the worksheet Data Validation range are in cells A1:A10 then you can fill the listbox by:

Code:
UserForm1.ListBox1.List = ThisWorkbook.Sheets("Sheet1").Range("A1:A10").Value

obviously modifying ranges and control/sheet names to suit.

If you are using a list constant typed into the validation dialog box to validate then you need to specify like so:

Code:
UserForm1.ListBox1.List = Array("Value1","Value2","Value3",...)


Hope this helps!

Best regards

Richard
 

bibstar03

New Member
Joined
Jan 30, 2003
Messages
25
Problem

I think i have followed your instructions correctly but its not working, what i did:

on the vm screen i selected my userform and created a list box, i then viewed the code for that, where i entered what you told me to, the 2 value are Single and Married

when i open the userform in excel the box says nothing

i have named the cell on my worksheet where the current list is 'status', and in vb i have written status in controlsource

this again may not make any sense if not i'll have another crack at explaining

thanks for ya time
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
You don't use the ControlSource property to populate a Combobox or Listbox on a userform.

You use the RowSource property.

If you were using a range for the data validation, say A1:A10, then just use that as the RowSource.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,009
Members
412,304
Latest member
citrus
Top