Combobox will not fill

[prahz]

Hello:

I am a relative novice at VBA programming but here goes...

In Excel, I am trying to fill a combobox ("ComboBox1") in a user form
("inputBox") through a dynamic named range in the "MasterDataSheet"
worksheet of my workbook. The dynamic named range is named
"ClientList" and is referenced with the formula
=OFFSET(MasterDataSheet!$B$1,0,0,1,COUNTA(MasterDataSheet!$1:$1)-1)

The fact that the list I want to populate into the combobox is across
columns rather than rows is giving me some difficulty. Below is the
code for my user form. Can someone please help me with where I am gong
wrong?

Option Explicit

Private Sub UserForm_Initialize()
Dim cName As Range
Dim ws As Worksheet
Set ws = Worksheets("MasterDataSheet")

For Each cName In ws.Range("ClientList")
With Me.ComboBox1
.AddItem cName.Value
.List(1, .ListCount - 1) = cName.Offset(0, 1).Value
End With
Next cName
End Sub

 

[Jon Peltier]

Use

For Each cName In ws.Range("ClientList").Cells

instead of

For Each cName In ws.Range("ClientList")

What are you trying to do here:

.List(1, .ListCount - 1) = cName.Offset(0, 1).Value

What the code says is in the second row of the combo box (first parameter,
0=first row), in the Nth column, where N is the number of items already
added, add something. I think you have your row and column indices reversed.
To add something in the second column of the last row of the list, where you
just added something to the first row of this column, try this:

.List(.ListCount - 1, 1)

Likewise I think your row and column index in the .Offset are reversed,
because you're saying use the next cell to the right, which would be the
next cell in the defined range. I suspect you mean to use values from the
next row below the defined range:

cName.Offset(1, 0).Value

Altogether, now:

.List(.ListCount - 1, 1) = cName.Offset(1, 0).Value

- Jon

 

[prahz]

Jon,

As you can see I had no clue...You are the man...thanks!