Excel-Visual Basic UserForm Coding

[MrDrJacob]

Been trying to do a VBA Excel sheet that calculates the cost of a pizza by size with x toppings. I have made a command button that calls a userform that lets the user select the size and toppings that the person wants. What im stuck on is getting the toppings added with the size the user selected to print to the messagebox, it prints just whatever the size of the pizza is right now. I also need to get the message box to tell the user what toppings they selected as well as the size, with the option to cancel the order but i think i can do that with an if-else statement. I just need help with the whole computation part. ill include the spreadsheet.PizzaProject.xlsm

[Kenneth Hobs]

cross-posted https://www.excelforum.com/excel-pro...rm-coding.html

[Paul_Hossler]

1. You should check out the policies about multi-posting (aka cross posting) -- see #4 in my sig

2. Try this macro and the attachment (added message and [Cancel] button)

3. You have prices in Col B but never use them

Private Sub cmdCancel_Click()
    Me.Hide
End Sub
Private Sub cmdOrder_Click()
    Dim curTotalCost As Double
    Dim curStartCost As Double
    Dim curToppings As Double
    Dim sOrder As String
    
    If optTwelve Then
        intTotalCost = 8#
        sOrder = "12"" Pizza with:" & vbCrLf & vbCrLf
    ElseIf optFifteen Then
        intTotalCost = 9#
        sOrder = "15"" Pizza with:" & vbCrLf & vbCrLf
    ElseIf optEighteen Then
        intTotalCost = 10#
        sOrder = "18"" Pizza with:" & vbCrLf & vbCrLf
    End If
    
    curToppings = 1.5
    
    If chkSausage Then
        intTotalCost = intTotalCost + curToppings
        sOrder = sOrder & "   Sausage" & vbCrLf
    End If
    
    If chkPepperoni Then
        intTotalCost = intTotalCost + curToppings
        sOrder = sOrder & "   Pepperoni" & vbCrLf
    End If
    If chkMushroom Then
        intTotalCost = intTotalCost + curToppings
        sOrder = sOrder & "   Mushroom" & vbCrLf
    End If
    If chkBacon Then
        intTotalCost = intTotalCost + curToppings
        sOrder = sOrder & "   Bacon" & vbCrLf
    End If
    If chkChicken Then
        intTotalCost = intTotalCost + curToppings
        sOrder = sOrder & "   Chicken" & vbCrLf
    End If
    
       
    Range("B7") = intTotalCost
    
    MsgBox sOrder & vbCrLf & vbCrLf & "Total Cost is: $" & intTotalCost
    
    Call ClearControls
    Range("B7") = " "
End Sub
Public Sub ClearControls()
    ' clear all controls
    chkSausage.Value = False
    chkPepperoni.Value = False
    chkMushroom.Value = False
    chkBacon.Value = False
    chkChicken.Value = False
    optTwelve.Value = False
    optFifteen.Value = False
    optEighteen.Value = False
    ' clear all controls
End Sub

[Kenneth Hobs]

Here is my solution. You can make coding easier if you set the Tag property values during design and not at userform initialize as I did. I would pick the 12" size as True for the default so that you don't have to check for one option as True as I did.

Private Sub UserForm_Initialize()  
  Dim c As Control
  On Error Resume Next
  For Each c In Controls
    c.Value = False
    Select Case TypeName(c)
      Case "OptionButton"
        c.Tag = 8
      Case "CheckBox"
        c.Tag = 1.5
      Case Else
    End Select
  Next c
  optFifteen.Tag = 9
  optEighteen.Tag = 10
End Sub


Private Sub cmdOrder_Click()
  Dim c As Control, d As Double
  If optTwelve = False And optFifteen = False And optEighteen = False Then
    MsgBox "You must pick a size."
    Exit Sub
  End If
  
  On Error Resume Next
  For Each c In Controls
    If c.Value = True Then d = d + c.Tag
  Next c
  
  MsgBox "Total Cost is: " & Format(d, "Currency")
  ClearControls
   With Range("B7")
     .Value = d
     .NumberFormat = "Currency"
   End With
End Sub


Public Sub ClearControls()
    ' clear all controls
    Dim c As Control
    On Error Resume Next
    For Each c In Controls
      c.Value = False
    Next c
End Sub