How to set up VBA form in Excel to input food order?

July 26, 2016 at 09:04:17
Specs: Windows 7

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.

Black Bean
Buffalo Chicken
Grilled Chicken
Soy Burger
Veggie Burger

American Cheese
Cheddar Cheese
Mozzarella Chees
Pepper Jack


Gluten Free Bun
Multigrain Bun
Onion Roll
Potato Roll
Ramen Bun
Whole Wheat Bun

message edited by phatguy85

See More: How to set up VBA form in Excel to input food order?

Reply ↓  Report •

July 27, 2016 at 04:13:30
I have developed a simple form with only meats on it, you can select which meats you want and hit the calculate button and it will give you the price.

I had to use another sheet to itemize the product and cost as per below

         A               B                C              D
1   Meats	       Cost	         Cheese	      Cost
2   Beef	         £5	   American Cheese	£1
3   Black Bean	         £2	   Cheddar Cheese	£2
4   Buffalo Chicken	 £15	   Mozzarella Chees	£4
5   Grilled Chicken	 £15	   Pepper Jack	        £5
6   Soy Burger	         £1		
7   Turkey           	 £22		
8   Veggie Burger	 £10		

I would personally split the catagories into different frames on your form, that way you can run the code which will take the values from each checkbox within each frame and check against the relevant list within the sheet and get the associated cost.

I cannot upload the file here so send me a PM with you email address and I can send it through, alternatively you will need to do the following

Insert a form - name it 'frmorder'
place a frame on the form - name it 'Fram1'
Place 7 checkboxes within the frame - named 'Checkbox1' - 'Checkbox7'
Place a command button on the form - named 'CommandButton1'
Place a label on the form - named 'Label1'

Paste the following code within the forms module

Dim SaleCost As Currency

Private Sub UserForm_Activate()
    i = 0
    SaleCost = 0
    For Each bcell In Sheets("Sheet2").Range("A2", Range("A" & Rows.Count).End(xlUp))
        FrmOrder.Frame1.Controls(i).Caption = bcell
        i = i + 1
    Next bcell
End Sub

Private Sub CommandButton1_Click()
    SaleCost = 0
    For i = 0 To FrmOrder.Frame1.Controls.Count - 1
        If FrmOrder.Frame1.Controls(i).Value = True Then
            For Each bcell In Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
                If bcell.Value = FrmOrder.Frame1.Controls(i).Caption Then
                    SaleCost = SaleCost + bcell.Offset(0, 1)
                End If
            Next bcell
        End If
    Next i
    Label1.Caption = "This sale: " & Format(SaleCost, "currency")
End Sub

message edited by AlwaysWillingToLearn

Reply ↓  Report •

July 27, 2016 at 07:38:13

looking at the code you send me, I have a recommendation, although it is ok to use .additem for every single checkbox you have, what would happen if you wanted to remove or add another checkbox, or change the captions of 10 0r 20 of them? it becomes unmanageable because you have to keep track of every checkbox and its position\index.

The above code I gave you will loop through the specified names that you enter into a sheet and will add these names of every checkbox on load up. This means you only have to manage your list and everything else just updates.

Please look at the workbook that I sent you it should make sense when you see it.

Reply ↓  Report •

July 27, 2016 at 07:54:25
This Makes sense with referencing Cells. I would like to make the Meats, Cheese, and Bun options as list drop downs to only allow the user to choose one. Then have a submit order button the calculates that sum as you saw in my code it prompts a message box showing summary of the items chosen from the list. So if your method for making the lists referencing cells work I am all for that too. thanks for the help!

Reply ↓  Report •

Related Solutions

Ask Question