PDA

View Full Version : Solved: Populate Combobox in userform



Barryj
06-03-2008, 03:07 AM
I would like to populate a combobox on my userform with names from a sheet named "Names".

The names start in cell A1 to A40, I had this working, forgot to save it and now carn't get it work of find the post where I found it.

Thanks for any assistance.

f2e4
06-03-2008, 03:13 AM
Create a Named Range of cells A1 - A40, e.g. MyNames

Open Properties of the combobox

Under ListFillRange, type in your Named Range, =MyNames

Barryj
06-03-2008, 03:18 AM
Will that work even if there are less that 40 names, someimes there may ony be 25.

f2e4
06-03-2008, 03:22 AM
Create a Dynamic Named Range which will only select the range of used cells

When defining the named range, instead of leaving it as =names!A1-A4

Type this:


=OFFSET('Names'!$A$1,0,0,MATCH("*",'Names'!$A:$A,-1),1)

Barryj
06-03-2008, 03:23 AM
Ok, thanks will give it a go.

Norie
06-03-2008, 07:04 AM
No need for named ranges, though they might be useful for other purposes.:)

LastRow = Worksheets("Names").Range("A" & Rows.Count).End(xlUp).Row

ComboBox1.RowSource = "Names!A1:A" & LastRow

Barryj
06-03-2008, 08:15 AM
I got this so far but still not working, any pointers
Private Sub ComboBox_DropClickButton
Dim A as Variant
Dim A as Long
LastRow = WorkSheets("Names").Range("A & Rows.Count).End(xlup).Row
ComboBox1.RowSource = "Names!A1:A" & LastRow
End Sub

f2e4
06-03-2008, 08:21 AM
What errors are you getting?

Did you try the offset function as it never fails for me?

Barryj
06-03-2008, 08:27 AM
Yes I tried that but nothing is showing in the comboBox with either of these, it is not erroring out it shows no data.

f2e4
06-03-2008, 08:29 AM
can you attach your worksheet for us to look at

Barryj
06-03-2008, 09:01 AM
I have included a test of the ComboBox sheet in the attached file, Thanks for the help.

Norie
06-03-2008, 09:38 AM
Barry

Well for a start there's a typo in the code it should be "A" not just "A.

And I wouldn't recommend populating a control with one of it's own events.

Try this.
Private Sub UserForm_Initialize()
LastRow = WorkSheets("Names").Range("A" & Rows.Count).End(xlUp).Row
ComboBox1.RowSource = "Names!A1:A" & LastRow
End Sub

f2e4
06-03-2008, 09:42 AM
Hi Barry

Fixed the typo

and you were trying to declare the same letter twice so moved the variant to public

See attached - its working now

Barryj
06-03-2008, 09:53 AM
Thankyou f2e4 that is working fine thanks again for your help and the others.

therealblind
07-12-2008, 08:42 AM
Thankyou f2e4 that is working fine thanks again for your help and the others.

Yeah, that's exactly what I needed!