Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2 column combo box (2002)

    Hi everybody:

    This was easy in Access, but I need a little help in Excel . . .

    I have a dynamic named range that returns 2 columns from a query table. I'd like to use it as the ListFillRange for a 2-column combo box; display column 2 and bind column 1 (hidden) so when my users make a selection, I can grab the ID number and do some other stuff with it.

    I'm starting out with a simple combo box control from the control toolbox, no code or anything fancy.

    Everything works fine if I just use a single column for a named range, but for some reason Excel is choking on my 2-column named range. I have set the column count to 2, bound column = 1, text column = 2, no column heads (my range doesn't include them, just the values), set the first column width to 0 and the second to 3 inches. Every time I type in the name of my range, Excel deletes it from the Properties box.

    Any ideas?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 column combo box (2002)

    I assume that you try to enter the name of the dynamic range in the ListFillRange (or RowSource, depending on where you use the combo box) property? That should work - I just now tested it in Excel 2002 SP-3.

    Could you attach a small sample workbook that demonstrates the problem?

    BTW, if you set the column width of the first column to 0, you can set the TextColumn property to the default value -1. This will use the first non-zero width column.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 column combo box (2002)

    Thanks, Hans. My file is attached.

    It's going to be a simple form, probably as a template.

    The idea is to have the users select a management company, then I'll return the funds for that company to another combo, where they'll select a fund; then they'll fill out the form (which I'll develop) for the fund, and I'll download the results to the .mdb via code.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 column combo box (2002)

    You have defined MgmtCoList as the union of two one-column ranges. You should define it as a single two-column range:

    =OFFSET(MgmtCos!$B$2,0,0,COUNTA(MgmtCos!$B:$[img]/forums/images/smilies/cool.gif[/img]-1,2)

    The last argument 2 specifies that the range contains two columns. The range will then work as ListFillRange for the combo box.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 column combo box (2002)

    Thanks, Hans! You're the best!

    Everything is easy when you know how.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •