Excel multicolumn list in form combo box

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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.
 

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.
 
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)
 
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
 

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,
 
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".

:)
 

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?
 

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.
 
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?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top