1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel multicolumn list in form combo box

Discussion in 'Business Applications' started by codeconfusion, Oct 10, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. codeconfusion

    codeconfusion Thread Starter

    Joined:
    Oct 10, 2008
    Messages:
    6
    Hi, I am very new to Excel userforms and code writing. My userform combo box drop down list gets its data from a single column dynamic range in a worksheet data table. But I need the combobox list to be displayed in seven columns, i.e. item#1 = row 0, column 0; item#2 = row 0, column 2; etc. Can you help me out with the Userform1 Initialize code to set this up, please.
    Cheers, codeconfusion
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You can't have columns in a combobox. For that, you'd need a listbox.

    Then you could set the listbox RowSource property to your dynamic (named, presumably) range*, and the ColumnCount property to (in your case) 7.

    Welcome to the board, HTH. :)

    *purists would say you should preferably use the .AddItem method during Initialize. If you want that instead, post back; I have code somewhere I could dig out for .AddItem where range columns >1.
     
  3. codeconfusion

    codeconfusion Thread Starter

    Joined:
    Oct 10, 2008
    Messages:
    6
    Thanks for your reply, Are you sure that I cannot have multiple columns. The properties window gives me the option of having ColumnCount for a combo box. Help (F1) tells me I can have more than one column in a ListBox or a ComboBox. Am I reading things wrongly? Also I downloaded some code like this:

    Private Sub UserForm_Initialize()

    With cboSelectDate
    cboSelectDate.ColumnCount = 7

    cboSelectDate.List(0, 0) = "Row 1, Col 1"
    cboSelectDate.List(1, 0) = "Row 1, Col 2"
    cboSelectDate.List(2, 0) = "Row 1, Col 3"
    cboSelectDate.List(3, 0) = "Row 1, Col 4"
    cboSelectDate.List(4, 0) = "Row 1, Col 5"
    cboSelectDate.List(5, 0) = "Row 1, Col 6"
    cboSelectDate.List(6, 0) = "Row 1, Col 7"

    End With
    cboSelectDate.TextColumn = 1

    End Sub

    But it is not working for me. Run time error #70. I really do want a combo box for this form.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK, so you can have multi-column combos. But:

    a) in that case, what's wrong with using RowSource?

    b) why would you? (over a listbox)
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    From here, for adapting:

    Private Sub UserForm_Initialize()
    Dim rng As Range

    With combobox1
    .Clear
    .ColumnWidths = "24"
    .ColumnCount = 2
    .List = Range("A2:B10").Value
    End With
    End Sub
     
  6. codeconfusion

    codeconfusion Thread Starter

    Joined:
    Oct 10, 2008
    Messages:
    6
    Hi, the .clear line of the code gives me an Unspecified Error message.
    The range that the data comes from is one column only, (supposed to be displayed in 7 cols in the drop down list). Also, if I am stating the number of columns etc. in the code, do I still enter that stuff in the properties window.
    Cheers,
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I'm no expert. In your situation, I'd transpose the vertical range to a "remote" horizontal location and use that as the RowSource. Dirty, but it would work.

    If Zack sees this, I'm sure he'll be only too pleased to detail "how to read a vertical range into a horizontal array".

    :)
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    codeconfusion, don't believe this!

    Well, that's true.... :rolleyes:
     
  9. codeconfusion

    codeconfusion Thread Starter

    Joined:
    Oct 10, 2008
    Messages:
    6
    Hi, I gathered from your previous threas that it would be easier to get the data to display in columns if I used a ListBox but I decided to try it and still the data displays in a single column list.
    As for the "dirty way", transposing the vetical range into a "remote" horizontal array...well...the array I guess, would have to be horizontal by 7 cols only and ?down...then be capable of growing (dynamic) by 1 entry each day and 1 row each week all by itself. Is that possible?
     
  10. codeconfusion

    codeconfusion Thread Starter

    Joined:
    Oct 10, 2008
    Messages:
    6
    Hi, I transposed the data into remote area and into the columns that I want and that is good but the transposed data doesn't grow when the original data grows so it is not entirely helpful. Thanks for your help.
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    CC, can you post a sample of your Excel file (with fake data) and enter into the sheet what it is that you want to display?
     
  12. codeconfusion

    codeconfusion Thread Starter

    Joined:
    Oct 10, 2008
    Messages:
    6
    Here is the workbook. I hop you can follow it.
     

    Attached Files:

  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/757780

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice