Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox will not fill
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(MasterDa taSheet!$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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox will not fill
Use
For Each cName In ws.Range("ClientList").Cells instead of For Each cName In ws.Range("ClientList") What are you trying to do he .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" wrote in message ps.com... 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(MasterDa taSheet!$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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox will not fill
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 he .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" wrote in message ps.com... 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(MasterDa taSheet!$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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill a Combobox from a ROW? | Excel Discussion (Misc queries) | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Better way to fill a ComboBox ? | Excel Programming | |||
Fill combobox from DLL | Excel Programming | |||
Fill a combobox | Excel Programming |