Coding for VBA Macro

[Shines0012]

I am trying to input data from my UserForm into a sheet I have already entered data without using the UserForm. For example, I have "Date", "Customer" in a drop down box , "Product" in a drop down box, "Quantity" , "Sales Price" As my data in the Userform. In my sheet, "Sheet 4", I have as headers, "Price" "Customer" "Product" "Quantity" "Sales Price" and "Total". My first empty row is 32 and I would like for the "Total" box on the sheet automatically generate to (6*Quantity) * Sales Price. I Can't figure out the code to transfer date from the userform to the sheet.

In addition, there are different sales prices for different products and I would like that to automatically generate in my userform. What would be the code to determine that?
For example, "That Green Drink" is $12.00 and "Energizing Elixir" is $10.00. How would I make sure those prices pop up in respect to the correct product?

 

[CephasOz]

Hi Shines0012. You have asked a lot in a few words.

To transfer your data from the userform to the worksheet, you need a "Save" button on your userform (call it something meaningful, like "btnSave"). Then, right-click it, select "View Code", and you can then enter the code that will write the data from the userform to the worksheet. For example, to transfer Date, Customer, and Product, the code would look like:

Private Sub btnSave_Click()
    Dim lngRow As Long
    With ThisWorkbook.Worksheets("Sheet 4")
        lngRow = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious, , , False).Row
        .Cells(lngRow, 1).Value = CDate(Me.cboDate.Value)   ' Change 1 to the column number for Date
        .Cells(lngRow, 2).Value = Me.cboCustomer.Value
        .Cells(lngRow, 3).Value = Me.cboProduct.Value
        ' continue the list of values
        .Cells(lngRow, 6).FormulaR1C1 = "=6*RC[-1]*RC[-2]"   ' Formula for Total
    End With
End Sub

For the prices of your items, you can use an "_AfterUpdate" event to display the price. For example, suppose you have a combobox control called "cboProduct" for Product, and a label just to the right of that called "lblProductPrice", underneath the code for "btnSave_Click", you could paste:

Private Sub cboProduct_AfterUpdate()
    Dim varResult As Variant
    Dim rngLookup As Range
    Set rngLookup = ThisWorkbook.Worksheets("Lookups").Cells(1, 1).CurrentRegion
    Set rngLookup = rngLookup.Offset(1).Resize(rngLookup.Rows.Count - 1)
    varResult = Application.VLookup(Me.cboProduct.Value, rngLookup, 2, False)
    If IsError(varResult) Then
        Me.lblProductPrice.Caption = "**NOT FOUND**"
    Else
        Me.lblProductPrice.Caption = Format(CCur(varResult), "$#,##0.00")
    End If
    Set rngLookup = Nothing
End Sub

I have assumed things about the lookup range, but you will probably want to replace "rngLookup" in the VLookup with your lookup range.

 

[Shines0012]

Thank you for your quick response!! That definitely cleared things up for me.

 

[CephasOz]

Glad to help.