Data Entry -Cell selection

[P.Jaimal]

Hi ,
My knowledge about excel programming is very limited. I have a
sheet with months in the first row. The first column contains names of
Products. The intervening grid contains the sales of these items for
these months.

A B C D E
1 APR MAY JUNE JULY
2 PRODUCT 1
3 PRODUCT 2
4 PRODUCT 3
5 PRODUCT 4

My userform has comboboxes "cboMonth" and "cboProduct".A text
box "txtSales".I would like to enter data into the sheet depending
on the selections of the two comboboxes.Example: if
cboMonth.Value="MAY" AND cboProduct.Value ="PRODUCT 3" then the
data from txtSales should get copied to the cell "C4".
I am unable to link these using code.My sheet is very long with many
products(and therefore many Rows).Hence a data entry form is very much
useful .
Please help.

P.Jaimal

 

[Jim Rech]

If we can assume the products and the months are in the same order on the
sheet as in the comboboxes then all you'd have to do is to use the
comboboxes' ListIndex properties to determine the offset of the target cell
from the top left of the table.

Something like:

Range("A1").Offset(ComboBox1.ListIndex + 1,ComboBox2.ListIndex + 1).Value =
txtSales.Value

The "+ 1" is because the ListIndex property starts at 0. You'd have to
fine-tune this example in any case.

If the opening assumption isn't correct then you'd have to add intermediate
steps to fine the row and column offsets using the Application.Match
function. Sort of like:

RowOffset = Application.Match(ComboBox1.Value, Range("A:A"), False)