Consulting

Results 1 to 4 of 4

Thread: Excel-Visual Basic UserForm Coding

  1. #1

    Excel-Visual Basic UserForm Coding

    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

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,333
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •