Question for dynamic range of excel combobox

[alsr]

Dear Sirs

Please help to answer my question

my dynamic range for combobox is as below

Private Sub UserForm_activate()
Sheets("123").Range("B2", Sheets("123").Range("b2").End(xlDown)).Name = "Dynamic"
uf1.cb1.RowSource = "Dynamic"
End Sub

It is work
but why I change my code to

Private Sub UserForm_activate()
Sheets("123").Range("B2", Sheets("123").Range("b2").End(xltoRight)).Name = "Dynamic"
uf1.cb1.RowSource = "Dynamic"
End Sub

It is not work.

I am very confusing about the root cause.

thank you for the time and knowledge

 

[Hui]

Alsr

Firstly, Welcome to the Chandoo.org Forums

Try this,
There is no need for an intermediate Named Range

Private Sub UserForm_activate()

  uf1.cb1.List = Application.WorksheetFunction.Transpose(Sheets("123").Range("B2", Sheets("123").Range("b2").End(xlToRight)))

End Sub

 

[alsr]

Dear Hui

Thank your for your method to solve the question.
it works!
I deeply appreciated your kindness

Can you tell me why xltoright is malfunction?
I'd tried several code to build the dynamic range, but it failed at all.
I am so so so desired to know why xlup or xldown works, but xltoleft or xltoright is malfunction.

Please forgive my bad English.

 

[Hui]

using

  uf1.cb1.List = Application.WorksheetFunction.Transpose(Sheets("123").Range("B2", Sheets("123").Range("b2").End(xlToRight)))

The Sheets("123").Range("B2", Sheets("123").Range("b2").End(xlToRight)) uses xltoRight as you required

But this is a 1,14 array, 1 Row, 14 Columns

The old part Range("B2", Sheets("123").Range("b2").End(xlDown))
Creates a 14, 1 array, , 14 Rows, 1 Column

The Control requires a Column of values and hence I use the Excel Transpose function to convert the horizontal to Vertical array

Which then is passed into the Control using the List Property directly, rather than via a Named Formula

You may have seen that using the Original XlToRight function only the first value, 2, was shown in the Dropdown list.

 

[alsr]

Dear Hui

I think I can understand what you mean.

Thank you for the reply and knowledge.Thanks again.