Populating Combo Box from Named Range

[TTom]

<b>Part 1 of routine:</b> Names range of cells as "rng_EmpSelection"
to be used to populate .rowsource in userform combo box

<b>Part 2 of routine:</b>
1. Populate userform (uf_Emp) combo box's .rowsource from named range in part 1
2. Set rows to display equal to rows populated
3. Display userform with combo box that should have drop down selection from range

<b>Problem:</b> When I run I have nothing in my combo box on down arrow...
Did not populate? Not certain where I messed up even after reading up on 'how to' Thoughts?

<i>The naming of range works as I can select on spreadsheet corner box and it highlights the range. I expect my error is in Part 2.

ListRows and RowSource in ComboBox setup are "0" and "blank" respectively prior to code.</i>

<code>
With Worksheets("Employees")
LastRow = .Range("H" & Rows.Count).End(xlUp).Row
.Range("H2:H" & LastRow).Name = "rng_EmpSelection"
End With

Load uf_Emp
uf_Emp.cbx_EmpSelect.RowSource = "rng_EmpSelection"
uf_Emp.cbx_EmpSelect.ListRows = LastRow
uf_Emp.Show
</code>

 

[GTO]

It worked fine for me, so a bit of a stab... You don't happen to have On Error Resume Next anyplace in the code, do you?

 

[TTom]

That's the entire code in this procedure and it looks right to me.
The user form only has the combo box at this time, very simple.
I try to simplify it when debugging so I can isolate things.

Worked for you?! That's got me scratching my head...

 

[GTO]

That's the entire code in this procedure and it looks right to me.
The user form only has the combo box at this time, very simple.
I try to simplify it when debugging so I can isolate things.

Worked for you?! That's got me scratching my head...

Do you have any error handling in any procedures that run prior to this? Otherwise, I am not spotting anything either

 

[Norie]

Why are you setting the no of rows to display to every row in the list?

Perhaps more importantly where us this code located?

The best place for this sort if code is usually in the userform's Initialize event.

 

[TTom]

I moved code to a new workbook with new userform and cbox
I moved the 'populate code' to Initialize event in userform.

I realized that the named range was made from CONCATENATE formulas.
I added 'copy to paste special value' to remove formulas so it was straight text.
I removed reference to rows I had in code... my error on that Norie

Everything works fine now on populating combo
I will now try moving back into original notebook and see if it works.
If not then I've got something elsewhere creating the problem.

Thanks for the assistance.

 

[Norie]

TTom

Do you mean the named range referred to a range that had a load of CONCATENATE formulas?

I just tried that and it worked OK, as long as calculation was set to Automatic.

Anyway, you've got it fixed.