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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"prahz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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,

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


Jon Peltier wrote:
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "prahz" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >