VBA USERFOM to Select Burger Options and Output Summary with Total

B

bjohnsman8

New Member
Joined
May 19, 2016
Messages
7
Hello,
I have a list of ingredients that I would like to develop a user form in VBA to allow the customer to select from a series of ingredients then it calculates the price at the end once you hit Order. Here is a list of the ingredients, I will not include the prices for each as they could be anything really. More specifically I want to assign the macro to a Button to prompt the input of the order in the user form. The follow ingredients sections should be listed with radio buttons. Once selected the user hits submit which calculates the total and gives a summary of the final order.

Meats
Beef
Black Bean
Buffalo Chicken
Grilled Chicken
Soy Burger
Turkey
Veggie Burger

Cheese
American Cheese
Cheddar Cheese
Mozzarella Chees
Pepper Jack

Toppings
Jalapenos
Lettuce
Mushrooms
Onions
Peppers
Pickles
Spinach
Tomato
Bacon

Bread
Gluten Free Bun
Multigrain Bun
Onion Roll
Potato Roll
Ramen Bun
Whole Wheat Bun
 
Sort by date Sort by votes
The follow ingredients sections should be listed with radio buttons
Click to expand...
Are you setting this up so one cannot have both lettuce and tomatoes on one's burger?

Also, if you continue with the radio button format, I'd suggest you add a No Cheese and No Toppings options to those sections.

What is your question in regards to this form?
 
Upvote 0
There is NO option for Beetroot.

So, in protest, I am not going to assist.
{joke, Joyce}
 
Upvote 0
The burger situation is just and example once the code is given it will be used in other ways. I just needed a starting form to build off of. Check boxes if that allows for multi selection in the topping section then I am game for that. Ultimate goal is to have a msgbox prompt at the end of order submission with the summary and total. Thanks in advanced.
 
Upvote 0
mikerickson said:
Are you setting this up so one cannot have both lettuce and tomatoes on one's burger?

Also, if you continue with the radio button format, I'd suggest you add a No Cheese and No Toppings options to those sections.

What is your question in regards to this form?
Click to expand...

This sounds good, my purpose as a newbie to VBA is get the base code and then build on the functionality to apply it to other scenarios. the burger option is just an example scenario.
 
Upvote 0
I am concerned that as you are a newbie, the following code will confuse you enormously.
I have, though needed to do this type of dynamic control creation to give a friendly interface to staff scheduling previously...
{Think of this in terms of Rooms having staff}

OK Firstly, the lists need to be stored in a sheet
[TABLE="width: 223"]
<tbody>[TR]
[TD]Meats[/TD]
[TD]Beef[/TD]
[/TR]
[TR]
[TD]Meats[/TD]
[TD]Black Bean[/TD]
[/TR]
[TR]
[TD]Meats[/TD]
[TD]Buffalo Chicken[/TD]
[/TR]
[TR]
[TD]Meats[/TD]
[TD]Grilled Chicken[/TD]
[/TR]
[TR]
[TD]Meats[/TD]
[TD]Soy Burger[/TD]
[/TR]
[TR]
[TD]Meats[/TD]
[TD]Turkey[/TD]
[/TR]
[TR]
[TD]Meats[/TD]
[TD]Veggie Burger[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cheese[/TD]
[TD]American Cheese[/TD]
[/TR]
[TR]
[TD]Cheese[/TD]
[TD]Cheddar Cheese[/TD]
[/TR]
[TR]
[TD]Cheese[/TD]
[TD]Mozzarella Cheese[/TD]
[/TR]
[TR]
[TD]Cheese[/TD]
[TD]Pepper Jack[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]Gluten Free Bun[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]Multigrain Bun[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]Onion Roll[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]Potato Roll[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]Ramen Bun[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]Whole Wheat Bun[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Jalapenos[/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Lettuce[/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Mushrooms[/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Onions[/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Peppers[/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Pickles[/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Spinach[/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Tomato[/TD]
[/TR]
[TR]
[TD]Toppings[/TD]
[TD]Bacon[/TD]
[/TR]
</tbody>[/TABLE]

And I start with a blank userform (no controls), though you would probably need a CommandButton or two
And everything prior to the positioning is data-driven (almost, you may note that toppings are Checkboxed instead of OptionButtoned, and there's a "NO CHEESE" option provided for Cheese)
I have used the boolean variable isTrue to pre-select the first option
 
Upvote 0
Code: 
Option Explicit
Dim iWidth As Long, iTop As Long, iLeft As Long
Dim isTrue As Boolean

Private Sub UserForm_Initialize()
Dim i As Long
    Call addFrames
    Call addOptions
    Call positionFrames
End Sub

Private Sub addFrames()
Dim sFrame As String
Dim ctrl As Object
Dim i As Long
    iTop = 9
    For i = 1 To 100
        If Trim(Cells(i, 1)) <> vbNullString Then
            If Trim(Cells(i, 1)) <> sFrame Then
                sFrame = Trim(Cells(i, 1))
                Call addControl(Me, "Frame", sFrame)
            End If
        End If
    Next
End Sub

Private Sub addOptions()
'Dim sFrame As String
Dim ctrl As Object
Dim i As Long
Dim sControlType As String
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" Then
            iTop = 7: iLeft = 3: sControlType = "OptionButton": isTrue = True
            If ctrl.Tag = "Toppings" Then
                sControlType = "CheckBox"
                isTrue = False
            ElseIf ctrl.Tag = "Cheese" Then
                Call addControl(ctrl, sControlType, "No Cheese", "None")
            End If
            For i = 1 To 100
                If Cells(i, 1) = ctrl.Tag Then
                    Call addControl(ctrl, sControlType, Cells(i, 2), CStr(i))
                End If
            Next
        End If
    Next
End Sub

Sub addControl(controlParent As Object, sControlType As String, sName As String, Optional sTag As String)
    If controlParent Is Nothing Then Exit Sub
    If sControlType = vbNullString Then Exit Sub
    With controlParent.Controls.Add("Forms." & sControlType & ".1")
        Select Case sControlType
            Case "Frame"
                .Top = iTop
                .Left = iTop
                .Width = Me.Width / 2 - 2 * iTop
                .Height = Me.Height / 2 - 2 * iTop
                .Tag = sName
                .Caption = sName
                iTop = iTop + 9
            Case "OptionButton", "CheckBox":
                .Caption = sName
                .Top = iTop
                .Left = iLeft
                .WordWrap = False
                .Width = Me.Width / 2
                .AutoSize = True
                If .Width > iWidth Then iWidth = Int(.Width - 0.0001) + 2
                .Tag = sTag
                .Value = isTrue
                isTrue = False
                iTop = iTop + Int(.Height - 0.0001) + 2
                controlParent.Height = iTop + 7
                controlParent.Width = iWidth + iLeft + 2
        End Select
    End With
End Sub

Private Sub positionFrames()
Dim ctrl As Object, ctrl1 As Object
Dim iTop As Long, iLeft As Long
' Code in here is specific.  For usability purposes, we want to position each frame
'  Breads top-left
'  Meats below it; with both framewidths the same
'  Cheese top, beside Breads
'  Toppings below it; with both framewidths the same
'  and tabindexes to match positioning
    iWidth = 0
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Bread" Then _
            ctrl.Top = 3: ctrl.Left = 3: iWidth = IIf(ctrl.Width > iWidth, ctrl.Width, iWidth)
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Meats" Then
            For Each ctrl1 In Me.Controls
                If TypeName(ctrl1) = "Frame" And ctrl1.Tag = "Bread" Then
                    ctrl.Left = ctrl1.Left
                    ctrl.Top = ctrl1.Top + ctrl1.Height + 2
                    iWidth = IIf(ctrl.Width > iWidth, ctrl.Width, iWidth)
                    ctrl.Width = iWidth
                    ctrl1.Width = iWidth
                End If
            Next
        End If
    Next
    iWidth = 0
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Cheese" Then
            For Each ctrl1 In Me.Controls
                If TypeName(ctrl1) = "Frame" And ctrl1.Tag = "Bread" Then
                    ctrl.Left = ctrl1.Left + ctrl1.Width + 2
                    ctrl.Top = ctrl1.Top
                    iWidth = IIf(ctrl.Width > iWidth, ctrl.Width, iWidth)
                End If
            Next
        End If
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Toppings" Then
            For Each ctrl1 In Me.Controls
                If TypeName(ctrl1) = "Frame" And ctrl1.Tag = "Cheese" Then
                    ctrl.Left = ctrl1.Left
                    ctrl.Top = ctrl1.Top + ctrl1.Height + 2
                    iWidth = IIf(ctrl.Width > iWidth, ctrl.Width, iWidth)
                    ctrl.Width = iWidth
                    ctrl1.Width = iWidth
                End If
            Next
        End If
    Next
    
' A bit of a cheat here.  Setting the tabindex to zero forces everything else to be greater
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Toppings" Then ctrl.TabIndex = 0: Exit For
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Cheese" Then ctrl.TabIndex = 0: Exit For
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Meats" Then ctrl.TabIndex = 0: Exit For
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Bread" Then ctrl.TabIndex = 0: Exit For
    Next
End Sub
 
Upvote 0
hicksi said:
Code: 
Option Explicit
Dim iWidth As Long, iTop As Long, iLeft As Long
Dim isTrue As Boolean

Private Sub UserForm_Initialize()
Dim i As Long
    Call addFrames
    Call addOptions
    Call positionFrames
End Sub

Private Sub addFrames()
Dim sFrame As String
Dim ctrl As Object
Dim i As Long
    iTop = 9
    For i = 1 To 100
        If Trim(Cells(i, 1)) <> vbNullString Then
            If Trim(Cells(i, 1)) <> sFrame Then
                sFrame = Trim(Cells(i, 1))
                Call addControl(Me, "Frame", sFrame)
            End If
        End If
    Next
End Sub

Private Sub addOptions()
'Dim sFrame As String
Dim ctrl As Object
Dim i As Long
Dim sControlType As String
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" Then
            iTop = 7: iLeft = 3: sControlType = "OptionButton": isTrue = True
            If ctrl.Tag = "Toppings" Then
                sControlType = "CheckBox"
                isTrue = False
            ElseIf ctrl.Tag = "Cheese" Then
                Call addControl(ctrl, sControlType, "No Cheese", "None")
            End If
            For i = 1 To 100
                If Cells(i, 1) = ctrl.Tag Then
                    Call addControl(ctrl, sControlType, Cells(i, 2), CStr(i))
                End If
            Next
        End If
    Next
End Sub

Sub addControl(controlParent As Object, sControlType As String, sName As String, Optional sTag As String)
    If controlParent Is Nothing Then Exit Sub
    If sControlType = vbNullString Then Exit Sub
    With controlParent.Controls.Add("Forms." & sControlType & ".1")
        Select Case sControlType
            Case "Frame"
                .Top = iTop
                .Left = iTop
                .Width = Me.Width / 2 - 2 * iTop
                .Height = Me.Height / 2 - 2 * iTop
                .Tag = sName
                .Caption = sName
                iTop = iTop + 9
            Case "OptionButton", "CheckBox":
                .Caption = sName
                .Top = iTop
                .Left = iLeft
                .WordWrap = False
                .Width = Me.Width / 2
                .AutoSize = True
                If .Width > iWidth Then iWidth = Int(.Width - 0.0001) + 2
                .Tag = sTag
                .Value = isTrue
                isTrue = False
                iTop = iTop + Int(.Height - 0.0001) + 2
                controlParent.Height = iTop + 7
                controlParent.Width = iWidth + iLeft + 2
        End Select
    End With
End Sub

Private Sub positionFrames()
Dim ctrl As Object, ctrl1 As Object
Dim iTop As Long, iLeft As Long
' Code in here is specific.  For usability purposes, we want to position each frame
'  Breads top-left
'  Meats below it; with both framewidths the same
'  Cheese top, beside Breads
'  Toppings below it; with both framewidths the same
'  and tabindexes to match positioning
    iWidth = 0
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Bread" Then _
            ctrl.Top = 3: ctrl.Left = 3: iWidth = IIf(ctrl.Width > iWidth, ctrl.Width, iWidth)
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Meats" Then
            For Each ctrl1 In Me.Controls
                If TypeName(ctrl1) = "Frame" And ctrl1.Tag = "Bread" Then
                    ctrl.Left = ctrl1.Left
                    ctrl.Top = ctrl1.Top + ctrl1.Height + 2
                    iWidth = IIf(ctrl.Width > iWidth, ctrl.Width, iWidth)
                    ctrl.Width = iWidth
                    ctrl1.Width = iWidth
                End If
            Next
        End If
    Next
    iWidth = 0
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Cheese" Then
            For Each ctrl1 In Me.Controls
                If TypeName(ctrl1) = "Frame" And ctrl1.Tag = "Bread" Then
                    ctrl.Left = ctrl1.Left + ctrl1.Width + 2
                    ctrl.Top = ctrl1.Top
                    iWidth = IIf(ctrl.Width > iWidth, ctrl.Width, iWidth)
                End If
            Next
        End If
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Toppings" Then
            For Each ctrl1 In Me.Controls
                If TypeName(ctrl1) = "Frame" And ctrl1.Tag = "Cheese" Then
                    ctrl.Left = ctrl1.Left
                    ctrl.Top = ctrl1.Top + ctrl1.Height + 2
                    iWidth = IIf(ctrl.Width > iWidth, ctrl.Width, iWidth)
                    ctrl.Width = iWidth
                    ctrl1.Width = iWidth
                End If
            Next
        End If
    Next
    
' A bit of a cheat here.  Setting the tabindex to zero forces everything else to be greater
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Toppings" Then ctrl.TabIndex = 0: Exit For
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Cheese" Then ctrl.TabIndex = 0: Exit For
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Meats" Then ctrl.TabIndex = 0: Exit For
    Next
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Frame" And ctrl.Tag = "Bread" Then ctrl.TabIndex = 0: Exit For
    Next
End Sub
Click to expand...
Here is the Code I Have with a User from having dropdown lists for everything except the toppings.

Private Sub UserForm_Click()
Meats.AddItem "Beef"
Meats.AddItem "Black Bean"
Meats.AddItem "Buffalo Chicken"
Meats.AddItem "Grilled Chicken"
Meats.AddItem "Soy Burger"
Meats.AddItem "Turkey"
Meats.AddItem "Veggie Burger"

ChseSel.AddItem "American Cheese"
ChseSel.AddItem "Cheddar Cheese"
ChseSel.AddItem "Mozzerella Cheese"
ChseSel.AddItem "Pepper Jack"

BrdSel.AddItem "Gluten Free Bun"
BrdSel.AddItem "Multigrain Bun"
BrdSel.AddItem "Onion Roll"
BrdSel.AddItem "Potato Roll"
BrdSel.AddItem "Ramen Bun"
BrdSel.AddItem "Whole Wheat Bun"
End Sub

Private Sub Cmb1_Click()

If Meats > 0 And ChseSel > 0 And BrdSel > 0 And TopSel > 0 Then
MsgBox "Meat: " & Meats.Value & vbNewLine & "Cheese: " & ChseSel.Value & vbNewLine & "Bread: " & BrdSel.Value

End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Separate One Cell Data into Multiple Rows
Replies
6
Views
1K
Fluff
Fluff
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
733
fjns
F
I
  • Question Question
How to get data the way I want it
Replies
1
Views
6K
BiocideJ
BiocideJ

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top