CODEWITHSUNDEEP
excelvbaspreadsheet
Fred James
Fred James

Reputation: 129

VBA spreadsheets columns

Private Sub txtTotal1_Change()
Dim Final As Integer
If cbOldf_Change() Then
    olf1 = cbOldf.ActiveCell.Offset(0, 1)
    Final = oldf_col * quantity
ElseIf cbHaribo1_Change() Then
    haribo1 = cbHaribo1.ActiveCell.Offset(0, 1) + 1
    Final = haribo_col * quantity
ElseIf cbOldf_Change() = cbHaribo1_Change() Then
    oldf_1 = cbOldf.ActiveCell.Offset(0, 1) + 1
    haribo1 = cbHaribo1.ActiveCell.Offset(0, 1) + 1
    Final = oldf_1 + haribo1 * quantity
End If
If txtQuantity1_Change() Then
    txtTotal.Value = Final
End Sub

I am new at vba. I have two worksheets for seperate products (haribo and old favourites) I have combo boxes to grab products from those worksheets and a text box to enter quantities. In the products worksheets the product name is in column A and the price is in column B. What I want to achieve is to be able to select a product name from the combo box, enter a quantity in the text box and have the total price be calculated and then displayed in a "total" textbox.

I am new at this so please look past my awful code, I have given it an honest 5 or so hours of effort and I am now at my wits end! I would love some help or to be pointed in the right direction please.

EDIT: Here is my workbook https://www.dropbox.com/s/49iym4exbcgmhcq/Main%20Page.xlsm I am doing this for college as you may guess by looking at it

Upvotes: 2

Views: 106

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

Try to use this code to set the total:

Private Sub SetTotal()
    Dim dblTotal As Double
    Dim dblQuantity As Double
    If IsNumeric(Me.txtQuantity1) Then
        dblQuantity = CDbl(Me.txtQuantity1)
    End If
    If cbHaribo1.Value <> "" Then
        dblTotal = dblQuantity * GetPrice(Sheets("HARIBO").Range("A:B"), cbHaribo1.Value)
    End If

    If Me.cbOldf.Value <> "" Then
        dblTotal = dblTotal + dblQuantity * GetPrice(Sheets("OLDFAVORITES").Range("A:B"), cbOldf.Value)
    End If
    Me.txtTotal1 = Format(dblTotal, "0.00")
End Sub

Private Function GetPrice(rng As Range, strProduct As String) As Double
    On Error GoTo ErrorHandler
    GetPrice = WorksheetFunction.VLookup(strProduct, rng, 2, False)
    Exit Function
ErrorHandler:
    GetPrice = 0
End Function

To trigger this code, you need to insert these events:

Private Sub cbHaribo1_Change()
    SetTotal
End Sub
Private Sub cbOldf_Change()
    SetTotal
End Sub    
Private Sub txtQuantity1_Change()
    SetTotal
End Sub

Note that the form is a bit ambiguous - at the moment the code applies to quantity to both, Haribo and Old Favorites. Better you either prodive a second quantity field - or insert an option box that greys out Haribo or Old Favorites...

Upvotes: 1

Related Questions