Solved: Populate Combobox in userform

[Barryj]

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]

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]

Will that work even if there are less that 40 names, someimes there may ony be 25.

[f2e4]

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]

Ok, thanks will give it a go.

[Norie]

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]

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]

What errors are you getting?

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

[Barryj]

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]

can you attach your worksheet for us to look at

[Barryj]

I have included a test of the ComboBox sheet in the attached file, Thanks for the help.

[Norie]

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]

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]

Thankyou f2e4 that is working fine thanks again for your help and the others.

[therealblind]

Thankyou f2e4 that is working fine thanks again for your help and the others.

Yeah, that's exactly what I needed!