none
VBA

    Question

  • I have a commandbuttons which when i click, it will be added in the listbox. now, my problem is how can i put a value in each item? Please Help me! I really need this!
    Monday, October 07, 2013 7:33 AM

Answers

  • Ok To make this work.. Do this first. Ensure that the values in Col F are numbers and formatted as relevant currency

    Here is the screenshot of a small scale model of what you are trying to do.

    Now use this code

    '~~> This variable will hold the total price
    Dim TotalPrice As Double
    
    '~~> Mocca Frappe
    Private Sub CommandButton1_Click()
        ListBox1.AddItem CommandButton1.Caption
        TotalPrice = TotalPrice + Val(Range("F7").Value)
    End Sub
    
    '~~> Coffee Jelly
    Private Sub CommandButton2_Click()
        ListBox1.AddItem CommandButton2.Caption
        TotalPrice = TotalPrice + Val(Range("F10").Value)
    End Sub
    
    '~~> Ok Button
    Private Sub CommandButton3_Click()
        MsgBox "The total of your order is " & TotalPrice
    End Sub
    

    Here is what happens when you add items and click on OK.

    Note: Using Excel Worksheet for this kind of functionality is not advisable. i would rather recommend porting it to VBA Userforms.


    Sid

    Monday, October 07, 2013 9:26 AM

All replies

  • You can use `.Additem`

    ListBox1.AddItem "Blah Blah"


    Sid

    Monday, October 07, 2013 7:35 AM
  • how can i put a price in each item?

    my code is this:

        

    Dim mf As String
    Dim i As Integer

    Private Sub CommandButton1_Click()
    mf = "Mocha Frappe"
    ListBox1.AddItem (mf)
    i = Val(160)
    End Sub

    is this right?


    Monday, October 07, 2013 8:13 AM
  • What is "mf"? What do you want to add? What role does "i" play here?

    Sid

    Monday, October 07, 2013 8:16 AM
  • mf and i are both variable. 
    Monday, October 07, 2013 8:20 AM
  • please help me!
    Monday, October 07, 2013 8:22 AM
  • Yes but what do you want to add? and what are their roles? Can you explain it a little more as to what do u want to achieve?

    Sid

    Monday, October 07, 2013 8:24 AM
  • i am doing some kind of Point of Sales. i have command buttons which when i click, it will be added in the listbox and im done with that. this is the code and it works

    Dim mf As String

    Private Sub CommandButton1_Click()
    mf = "Mocha Frappe"
    ListBox1.AddItem (mf)
    End Sub

    I also have the Remove button that deletes the selected item in the Listbox and this is the code:

    Private Sub CommandButton11_Click()
    ListBox1.RemoveItem ListBox1.ListIndex
    End Sub

    Now, what i want to happen is to put prices in every item that when i click the ok button, a message box will appear and compute the items in the listbox and show the total price. Help please! 

    Monday, October 07, 2013 8:35 AM
  • I am still unable to understand. Let's say You added these items to the listbox

    Mocha Frappe

    Hot Chocolate

    Cappuccino

    Now where do u want to add the prices?


    Sid

    Monday, October 07, 2013 8:45 AM
  • i want the mocha frappe etc. already have the price so that when i click ok the total price will be computed.
    Monday, October 07, 2013 8:56 AM
  • If you dont mind, sir. do you have a facebook account? so we can converse properly and send you some screenshots of my program as well for you to understand it.
    Monday, October 07, 2013 9:00 AM
  • My Facebook account is only for my personal use ;) You may upload screenshots to Skydrive and then post the links here.

    Sid

    Monday, October 07, 2013 9:04 AM
  • I, understand. Here it it Sir, this what my program looks like.

    http://social.msdn.microsoft.com/Forums/getfile/350368

    Monday, October 07, 2013 9:12 AM
  • Oh Ok. I understand it now. :)

    Last question. You want the total to show in the listbox itself or in a messagebox? And when? When the user presses "OK"?


    Sid

    Monday, October 07, 2013 9:15 AM
  • In a MessageBox, Sir. The user will press the ok button if the user is done selecting his order. and after he click the ok, a message box will appear with the computed total price.
    Monday, October 07, 2013 9:19 AM
  • Ok To make this work.. Do this first. Ensure that the values in Col F are numbers and formatted as relevant currency

    Here is the screenshot of a small scale model of what you are trying to do.

    Now use this code

    '~~> This variable will hold the total price
    Dim TotalPrice As Double
    
    '~~> Mocca Frappe
    Private Sub CommandButton1_Click()
        ListBox1.AddItem CommandButton1.Caption
        TotalPrice = TotalPrice + Val(Range("F7").Value)
    End Sub
    
    '~~> Coffee Jelly
    Private Sub CommandButton2_Click()
        ListBox1.AddItem CommandButton2.Caption
        TotalPrice = TotalPrice + Val(Range("F10").Value)
    End Sub
    
    '~~> Ok Button
    Private Sub CommandButton3_Click()
        MsgBox "The total of your order is " & TotalPrice
    End Sub
    

    Here is what happens when you add items and click on OK.

    Note: Using Excel Worksheet for this kind of functionality is not advisable. i would rather recommend porting it to VBA Userforms.


    Sid

    Monday, October 07, 2013 9:26 AM
  • sir do i really need to replace all my codes? Here is my original code.

        

    Option Explicit
    Dim mf As String
    Dim cj As String
    Dim jc As String
    Dim cl As String
    Dim cm As String
    Dim bf As String
    Dim bc As String
    Dim cr As String
    Dim i As Integer

    Private Sub CommandButton1_Click()
    mf = "Mocha Frappe"
    ListBox1.AddItem (mf)
    End Sub

    Private Sub CommandButton2_Click()
    cj = "Coffee Jelly"
    ListBox1.AddItem (cj)
    End Sub
    Private Sub CommandButton3_Click()
    jc = "Java Chip"
    ListBox1.AddItem (jc)
    End Sub

    Private Sub CommandButton4_Click()
    cl = "Caramel Latte"
    ListBox1.AddItem (cl)
    End Sub

    Private Sub CommandButton5_Click()
    cm = "Choco Muffin"
    ListBox1.AddItem (cm)
    End Sub

    Private Sub CommandButton6_Click()
    bf = "Blackforest Slice"
    ListBox1.AddItem (bf)
    End Sub

    Private Sub CommandButton7_Click()
    bc = "Blueberry Cake"
    ListBox1.AddItem (bc)
    End Sub

    Private Sub CommandButton8_Click()
    cr = "Cinnamon Roll"
    ListBox1.AddItem (cr)
    End Sub


    Monday, October 07, 2013 9:39 AM
  • No you can use what you are using. But you need to add up the cost as I have shown up so that you can then show it in the "OK" button.

    Sid

    Monday, October 07, 2013 9:48 AM
  • oh ok Sir, so how can I ensure the values in Col F are numbers and formatted as relevant currency?


    Monday, October 07, 2013 9:52 AM
  • Type the numbers manually and then right click on the cell and click on "format Cells". Under the number tab, you will see Currency. Select the relevant currency :)

    Sid

    Monday, October 07, 2013 9:55 AM
  • but Sir this is how it looks like when i click format cell, where is the number tab there? Im using MS Ecxel 2007

    http://social.msdn.microsoft.com/Forums/getfile/350404

    Monday, October 07, 2013 10:05 AM
  • When you will "right click" on the cell you will then get "Format Cells" Click there.

    Sid

    Monday, October 07, 2013 10:14 AM
  • Thank you Sir! but I have one more problem. Is it possible for the input box to appear right after i click the ok button in the message box? and that input box will require the user to enter the amount tendered.
    Monday, October 07, 2013 10:31 AM
  • Yes.

    '~~> Ok Button
    Private Sub CommandButton3_Click()
        Dim Ret
        
        Ret = Application.InputBox("Please enter the amount")
        
        If Ret = False Then Exit Sub
        
        MsgBox Ret
        
        'MsgBox "The total of your order is " & TotalPrice
    End Sub
    


    Sid

    Monday, October 07, 2013 10:33 AM
  • but Sir, how would the user know his bill if the input box will come first before the message box? what i want to happen is to know the user his bill first before he enter his tendered amount. Please help me Sir! 
    Monday, October 07, 2013 10:44 AM
  • Put the input box after the messagebox ;)

    Sid

    Monday, October 07, 2013 10:51 AM
  • Thank you sir! but one more thing please, after the message box, i want another message box to appear again and display the amount tendered, the the total amount and the change. Please help :)
    Monday, October 07, 2013 10:58 AM
  • Sir, are you still there?
    Monday, October 07, 2013 11:20 AM
  • I don't want to discourage you but you can't ask different questions in 1 question :) It's not conducive for the learning of others. if your original question is solved, please close this question and then raise a separate thread for a separate question. I have already answered at least 5 different questions in this thread :) At this rate this question will never be closed :)

    Ok to answer your last question. Your logic should be

    Show Messagebox

    Show InputBox

    Do Calculations

    Show message box again

    Now you tell me how should your code look ;)


    Sid

    Monday, October 07, 2013 11:23 AM
  • I'm sorry Sir if I've been too much. But I want you to know how thankful I am for helping me. You may think that I abused you, but it's not my intention. Thank you for all the help and effort you exerted for me! :)
    Monday, October 07, 2013 11:33 AM
  • No you have not offended me or used me in any ways :) Hope your query is solved?

    Sid

    Monday, October 07, 2013 11:40 AM