VBA USERFOM to Select Burger Options and Output Summary with Total

[bjohnsman8]

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

 

[mikerickson]

The follow ingredients sections should be listed with radio buttons

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?

 

[Trebor76]

For reference this has been cross-posted here.

 

[hicksi]

There is NO option for Beetroot.

So, in protest, I am not going to assist.
{joke, Joyce}

 

[bjohnsman8]

I am looking for the VBA to set that up specifically. I am ok with the options you suggested.

 

[bjohnsman8]

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.

 

[bjohnsman8]

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?

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.

 

[hicksi]

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

Meats	Beef
Meats	Black Bean
Meats	Buffalo Chicken
Meats	Grilled Chicken
Meats	Soy Burger
Meats	Turkey
Meats	Veggie Burger
Cheese	American Cheese
Cheese	Cheddar Cheese
Cheese	Mozzarella Cheese
Cheese	Pepper Jack
Bread	Gluten Free Bun
Bread	Multigrain Bun
Bread	Onion Roll
Bread	Potato Roll
Bread	Ramen Bun
Bread	Whole Wheat Bun
Toppings	Jalapenos
Toppings	Lettuce
Toppings	Mushrooms
Toppings	Onions
Toppings	Peppers
Toppings	Pickles
Toppings	Spinach
Toppings	Tomato
Toppings	Bacon

<tbody>
</tbody>

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

 

[hicksi]

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

 

[bjohnsman8]

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

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