Comboboxes with multiple values

[tinaw]

Hello, I am trying to create a combobox in Excel that would display
multiple values such as:

Tank Top Blue Large $30.00
Tank Top Red Medium $20.00

Etc.

It could have 500 items or more in the Combobox. I would of course have
the values from a range in another sheet in that workbook.

I want the user to be able to type a "T" for example to access all of
the inventory that starts with "T" for Tank Top or "B" for Bikini etc.,
to narrow the inventory in the combobox down.

When the user selects a value such as the Blue Tank Top, as above I
want cells in Excel to be be populated, for example:

Description Color Size Price

The user could select as many as 50 different items so everytime the
user clicks in a cell underneath, I want the combobox to show (be copied
to many cells). In experimenting, I noticed that if the user clicks in a
cell, the combobox shows up and if the user selects a value then changes
their mind, they cannot delete the entry to clear the cell.

So basically it is a order sheet, but you know human nature, people
change their minds. The user must not be able to edit the cell (change a
value), but if they make a mistake and select something in the combobox
and do not want it after all, I want the cell to be empty or if the user
wants to change the item, I want the value to be able to be changed.

After the user selects all the items they want, I will have Excel
calculate the totals of all the products.

I know how to create a user form, but I do not want to user one, I just
want to use the combobox and show multiple values and have the
coordinating cells populated.

Too much to ask? I used to program in VBA and just need a refresher.

I would greatly appreciate any help that is provided.

Kind Regards,

Tina

 

[Patrick Molloy]

not sure what you're asking.

the code below populates a table in cells B3:E102 into a userform's
combobox with four columns...

Option Explicit
Private Sub UserForm_Initialize()
loadCombobox1
End Sub
Sub loadCombobox1()
Dim cell As Range
Dim index As Long
Dim col As Long
With ComboBox1
.ColumnCount = 4
For Each cell In Range("B3:E102").Columns(1).Cells
.AddItem cell.Value
index = .ListCount - 1
For col = 1 To 3
.List(index, col) = cell.Offset(, col)
Next
Next
End With
End Sub

 

[tinaw]

Patrick,

Thanks for your reply, but I really did not want to use a user form.
What I wanted to do was to have an order form in Excel, say in Sheet1.
The user will click on a cell to order the first product and a drop down
list will appear showing products from the inventory list from Sheet2.
In Sheet2 there will be 5 columns showing:
Description, Color, Size, Product Code, Price.

Something like:
Tiki Tank Red Small TK2009 $30.00

There could be about 100 products showing. This is what I want to show
up in the dropdown list on Sheet1, say cell A1 for example. When the
user selects a product from the dropdown list (combobox), I want the
corresponding cells to be populated on the order form, for example: A1,
B1, C1, D1 and E1.

The user then could select many products, so when they click in the
cell below (for example, cell A2), the combobox would show up again so
they could select another product.

If the user selects a product and then changes their mind, I want them
to be able to press delete (like ESC) and they can just undo what they
were doing.

I hope this makes sense. So when they are done it would look something
like the following:

DESCRIPTION COLOR SIZE PRODUCTCODE
PRICE
Tiki Tank Red Small TK2009
$30.00
Jogging Pants Blue Medium JP2008
$25.00
Sweater Green Medium SW2009
$30.00

Thanks for all of your help,

Tina

 

[Patrick Molloy]

you obviously discarded using the Advanced Filter for this?

 

[Patrick Molloy]

ok
you can use the ActiveX combobox
set the column count in its properties to 5 and set the ListFillRange to
your data table, eg sheet2!A1:E200

put in cell A1 the number 1, this will be the order index which will
increment

in the sheet's code window (richt click the sheet tab and select view code)
paste this:

Option Explicit
Private Sub ComboBox1_Change()
Dim index As Long
index = Range("A1").Value
With ComboBox1

Cells(index, 5) = .List(index, 0)
Cells(index, 6) = .List(index, 1)
Cells(index, 7) = .List(index, 2)
Cells(index, 8) = .List(index, 3)
Cells(index, 9) = .List(index, 4)

Range("A1") = index + 1
End With

End Sub



now, when you click an item in the combobox, it copies the data from the
combo to cells in the row indicated by the index (from cell A1 ) and columns
E through I (5 to 9)
then the index is incremented by one
click the combo again, and the next set of 5 values is pasted to the new
table

add an offset if you want headers for columns E-I

is this working along the right lines?