Implementing Pricing Logic and Data Storage in Excel VBA

1. Product Pricing and Discount Calculation

Handling Quantity Change (c1_Change)

Private Sub c1_Change()
    cantidad = c1
End Sub

Calculating Total Price and Discounts (calcular_Click)

This procedure calculates the total price based on the selected product and quantity, applies conditional discounts, and determines the final amount payable, including VAT (19%).

Private Sub calcular_Click()
    Dim total As Double
    Dim dcto As Double
    
    ' Calculate base total based on product price
    If producto = "Arroz" Then
        total = 890 * cantidad
    End If
    
    If producto = "Fideos" Then
        total = 580 * cantidad
    End If
    
    If producto = "Torta Piña" Then
        total = 2690 * cantidad
    End If
    
    ' Apply conditional discounts
    If dia = "Lunes" And producto = "Fideos" Then
        dcto = total * 0.5 ' 50% discount
    End If
    
    If dia = "Martes" And producto = "Arroz" Then
        dcto = total * 0.2 ' 20% discount
    End If
    
    If dia = "Otro" And producto = "Torta Piña" Then
        dcto = total * 0.15 ' 15% discount
    End If
    
    ' Final calculations
    neto = total
    iva = (neto - dcto) * 0.19
    totalpagar = neto - dcto + iva
End Sub

UserForm Initialization (UserForm_Initialize)

Sets up the available options for products and days upon form loading.

Private Sub UserForm_Initialize()
    producto.AddItem "Fideos"
    producto.AddItem "Arroz"
    producto.AddItem "Torta Piña"
    dia.AddItem "Lunes"
    dia.AddItem "Martes"
    dia.AddItem "Otro"
End Sub

2. UserForm Display and Worksheet Formatting

Opening the UserForm (Btn_Abrir_Click)

Private Sub Btn_Abrir_Click()
    UserForm1.Show
End Sub

Setting Table Headers and Formatting (Btn_Tabla_Click)

This procedure formats the header row (A1:J1) by applying font styles, size, and color, sets the column titles, and defines background colors for the header and data area.

Private Sub Btn_Tabla_Click()
    ' Apply font formatting to the header range A1:J1
    Range("A1:J1").Font.Bold = True
    Range("A1:J1").Font.Italic = True
    Range("A1:J1").Font.Underline = True
    Range("A1:J1").Font.Size = 14
    Range("A1:J1").Font.Name = "Arial"
    Range("A1:J1").Font.Color = RGB(25, 150, 35) ' R=RED G=GREEN B=BLUE
    
    ' Set column headers
    Range("A1") = "PELICULA"
    Range("B1") = "CANTIDAD ENTRADAS"
    Range("C1") = "PAPAS FRITAS"
    Range("D1") = "BEBIDA"
    Range("E1") = "POPCORN"
    Range("F1") = "CANTIDAD COMBO"
    Range("G1") = "DESCUENTO"
    Range("H1") = "TOTAL NETO"
    Range("I1") = "TOTAL IVA"
    Range("J1") = "TOTAL PAGAR"
    
    ' Set cell background colors
    Range("A1:J1").Interior.Color = RGB(255, 255, 0) ' Yellow header
    Range("A2:J100").Interior.Color = RGB(150, 250, 0) ' Light green data area
End Sub

3. Simple Pricing and Data Persistence

Handling Quantity Change (c1_Change)

Private Sub c1_Change()
    Cantidad = c1
End Sub

Calculating Price and VAT (Calcular_Click)

Calculates the total value, VAT (19%), and final price based on the selected product type.

Private Sub Calcular_Click()
    Dim vt As Double ' Unit Value
    
    If Tipo = "Arroz" Then
        vt = 790
    End If
    
    If Tipo = "Tallarines" Then
        vt = 950
    End If
    
    Dim n As Double ' Net amount
    Dim i As Double ' IVA/VAT amount
    Dim p As Double ' Total payable
    
    n = vt * Cantidad
    i = n * 0.19
    p = n + i
    
    neto = n
    iva = i
    pagar = p
End Sub

Saving Data to Worksheet (Guardar_Click)

Iterates through rows 2 to 100 to find the first empty row in column A and saves the transaction details (index, quantity, discount, net, IVA, total pay).

Private Sub Guardar_Click()
    For i = 2 To 100
        If (Range("a" & Trim(Str(i)))) = "" Then
            Range("a" & Trim(Str(i))) = i - 1
            Range("b" & Trim(Str(i))) = Cantidad
            Range("c" & Trim(Str(i))) = dcto
            Range("d" & Trim(Str(i))) = neto
            Range("e" & Trim(Str(i))) = iva
            Range("f" & Trim(Str(i))) = pagar
            Exit For
        End If
    Next
End Sub

UserForm Initialization (UserForm_Initialize)

Sets up the available product types.

Private Sub UserForm_Initialize()
    Tipo.AddItem "Arroz"
    Tipo.AddItem "Tallarines"
End Sub