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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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,351
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,117
Messages
5,857,479
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top