Dynamic range in combobox list

Question

I'm using two methods to build a dynamic list of names in the combo box row source. In the first instance i use:

With Sheet2

RateComboBox.RowSource = "'" & .Name & "'!" & .Range("b9", .Range("c33").End(xlUp)).Address End With

The control source is set to a cell on a sheet and the bound column is column 1, or the range b9:b33 which has the numbers 1-25 and the column count is 2 which the second column list names (1-25) as they are added. It works great until the range C9:C33 is full at which time the drop down list shows a bogus list with items (text) that start now in B1. What is happening?

In the second instance I use:

Me.ComboBox1.RowSource = Worksheets("Form").Range("B8", Range("B32").End(xlUp)).Address

which the range b8:B32 is populated with names as they are added there. the control source is set to a cell on a sheet and the bound column is column 1. Again it works great until the (list) range is full. In both instances I only want items (names) in the combo box to be listed as they are added in those ranges. ??

If cell 32 has text, it runs till the next empty cell... like in the first example, where you should start with 34 or go down from the first cell (9) — Dirk Reichel
what is interesting is that it is listing text that is above b9 in the first example and the same is true for the second example. How should I code this? — SBV
Check for the last cell to be empty... if yes: do as it is now and it has text inside just pick the last one directly... — Dirk Reichel

Dirk Reichel Dirk Reichel · Accepted Answer · 2016-03-03T05:36:49

For the first part like this:

If Len(.Range("c33").Value) Then
  RateComboBox.RowSource = "'" & .Name & "'!B9:C33"
Else
  RateComboBox.RowSource = "'" & .Name & "'!" & .Range("b9", .Range("c33").End(xlUp)).Address
End If

For the second part it is as good as the same:

If Len(Range("B32").Value) Then
  Me.ComboBox1.RowSource = Worksheets("Form").Range("B8:B32").Address
Else
  Me.ComboBox1.RowSource = Worksheets("Form").Range("B8", Range("B32").End(xlUp)).Address
End If

Dynamic range in combobox list

1 Answers