PDA

View Full Version : Excel-Visual Basic UserForm Coding



MrDrJacob
12-03-2017, 04:55 PM
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.21103

Kenneth Hobs
12-03-2017, 05:17 PM
cross-posted https://www.excelforum.com/excel-programming-vba-macros/1210976-excel-visual-basic-userform-coding.html

Paul_Hossler
12-03-2017, 05:49 PM
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
12-03-2017, 05:57 PM
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