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

 

[Guest]

This assumes the worksheet name is "Sheet1". Change the name in the code to
suit. Also assumed is that there is an Apply button on the UF that pastes the
sales data to the appropriate cell. The name of this button (CommandButton)
is cbApply and its Enable property is set to False.

Paste to the UF code module. Done in a hurry and minimal testing.

Dim ws As Worksheet

Private Sub cboMonth_Change()
cbApply.Enabled = EnableApplyBtn
End Sub
Private Sub cboProduct_Change()
cbApply.Enabled = EnableApplyBtn
End Sub
Private Sub txtSales_Change()
cbApply.Enabled = EnableApplyBtn
End Sub

Private Sub UserForm_Activate()
Dim r As Range, r2 As Range
Dim i As Integer

Set ws = Sheets("Sheet1") 'Change ws name to suit
Set r = ws.Range(ws.Cells(2, 1), ws.Cells(2, 1).End(xlDown))
Set r2 = ws.Range("B1:M1")
cboProduct.RowSource = r.Address
For i = 1 To 12
cboMonth.AddItem r2(i)
Next
End Sub

Private Sub cbApply_Click()
Dim m As String, p As String, s As String
Dim r As Range, c As Range, cc As Range

m = cboMonth.Value
p = cboProduct.Value
s = txtSales.Value
Set r = ws.UsedRange
Set c = r.Find(p)
Set cc = r.Find(m)
r.Cells(c.Row, cc.Column) = s
End Sub

Private Function EnableApplyBtn() As Boolean
EnableApplyBtn = (Len(cboProduct) > 0 And Len(cboMonth) > 0 And
Len(txtSales) > 0)
End Function

Regards,
Greg

 

[Guest]

As an alternative to using a data entry form, note that you can split the
screen such that the header cells (Months) are always visible when you scroll
down through the product list. To split the screen, use the mouse pointer to
drag down the thin bar at the extreme top of the vertical scroll bar such
that one row behind the current window is visible. This is the same as
running the code:
ActiveWindow.SplitRow = 1
Now scroll down the product list. The month headers (of the 2nd window)
should remain visible.

Also note that you shouldn't repost until after approx. 24 hours. Your first
post likely would have been answered sometime today.

Regards,
Greg