none
Getting Information from Listbox1 and Listbox 2 to populate in next row of Sheet 1 RRS feed

  • Question

  • Hello All.

    I have been scratching my head and searching the wide web for answers and have not found it. Im still trying to learn as I go with VBA, so please help a newbie out!

    I am building a retail form, generating 2 separate listboxes (list, list2) from input of textboxes. "list" is for taxable items, "list2" is for non-taxable items. They are both on one userform. Both lists have 3 columns of information(item,price,quantity). I am trying to use a command button(checkout) to populate all the information that is in both (list, and list2) into sheet1 of my workbook. I would like the values to be formatted as currency and populated without overwriting previously submitted information.

    Question 2- Along with populating this information into sheet 1. I would like it to populate into sheet 2 to be used as a "receipt". I'd like the information to be added in sheet2 to a more specific range of cells, so i can have it populated in between the business name, address, etc (at the top) and the tax, subtotal etc(at the bottom).

    I have figured out how to populate both listboxes from textbox input, but I havent figured out how to get the information in those listboxes back into the workbook.

    VBA is great, I appreciate all the help and support given and received through this forum. 

    Part of what I have in my code:

    Private Sub checkout_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("sheet1")

    LastRow = Worksheets("Sheet1").Range("A2").CurrentRegion.Rows.Count
    Cells(LastRow + 1, 1).Value = Me.List.List
            (Cells(1, 1), Cells(Me.List.ListCount, Me.List.ColumnCount)) = _
                Me.List.List
                   

    End Sub

    Saturday, November 4, 2017 4:26 AM

Answers

  • I have an appointment to keep so have not got time to test at the moment.

    However, try converting your second textbox to a combobox then I think that it will probably work. The second parameter of the called sub is declared as a combobox (not textbox) and you are passing a textbox to it. See the second parameter in the following called sub.

    Private Sub Add_Click_Prim(ByRef List As msforms.ListBox, _
        ByRef itembox As msforms.ComboBox, ByRef PriceBox As msforms.TextBox, ByRef QuantityBox As msforms.TextBox)


    Regards, OssieMac

    • Marked as answer by dross85 Tuesday, March 13, 2018 9:08 PM
    Monday, November 6, 2017 10:56 PM

All replies

  • Is this a multi select listbox (ie. do you have the MultiSelect property set to 1 - fmMultiSelectMulti so you can select multiple items)?

    Regards, OssieMac

    Saturday, November 4, 2017 11:04 AM
  • thanks for the reply OssieMac-

    yes, it is a multi select. I have a "remove" command so I am able to remove a single item if needed from either listbox, the property is set to -1. I can remove a selected item, however it does not recalculate the total (calculated in a separate textbox) when the item is removed, the sum only gets updated when I add each item...another code im trying to figure out.

    Saturday, November 4, 2017 4:40 PM
  • I do not understand all that you want to do. I really need a copy of your workbook with an explanation of what data needs to be copied to where.

    If you upload the workbook to OneDrive then I can have a look at it.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

    Regards, OssieMac

    Saturday, November 4, 2017 10:54 PM
  • OssieMac-

    Thank you for continuing to help! Here is my link-

    https://1drv.ms/x/s!ArTfUcJS-eVihWzdeShRn_CGFUTb

    in the excel file and the macro code I have included writing in green, explaining what I'm trying to do. But if you run the macro, and see how its acting, maybe you can straighten up my code and help me solve these issues, or simplify the code so it will work, calculate and populate as I've been trying.

    Sunday, November 5, 2017 12:44 AM
  • I have modified the code for the Checkout button.

    When finding the next blank row instead of looking for next blank cell on each column just find the next blank cell on the first column and assign it to a range variable. Then use the range variable for the first field entry and then offset to the right to the required columns for price and quantity. Reason for this is if a price had not been entered for an item then if you found the next blank cell in each column then the row following the blank price would cause the price to be placed in the row above and everything is out of kilter. Of course you could add some code to ensure that there are no blanks in the data.

    I have used row insert on Sheet2 so that the totals block is not over written. You will need code to clear sheet1 and sheet2 when required and delete rows in sheet2 to bring the totals block up to required position. However, personally I think that I would simply recreate the totals block under the data after copying the data to it and then all can be cleared. You could even create an example totals block on another worksheet and copy and paste it to the required location after data is copied to the sheet.

    I also wonder why you are using textboxes for the item, price and qty. You could use a ComboBox and the RowSource from a worksheet with the list of products and adjacent column with price. If you keep the list sorted on the item names then as you commence typing then it auto matches to values in the Combo DropDown list and you then only need to select from a small group of choices. You need event code on the ComboBox like the following to auto display the dropdown when you initially click in the control.

    Private Sub ComboBox1_Enter()
        ComboBox1.DropDown
    End Sub

    Following code for the CheckOut button. See if it is doing what you require and feel free to get back to me if any problems.

    Private Sub checkout_Click()
    'this is where I want to have both List1 and list2 generate to sheet1 to record the sale and items
    'i would also like this to input to sheet2 as a list of items to print as a reciept. Id like the items to show up inbetween the existing information.

        Dim lngItem As Long
        Dim ws As Worksheet
        Dim rngDestin As Range
        Dim i As Long
       
        For i = 1 To 2
            'Next line create the worksheet name from i so do not need to duplicate code for the second sheet.
            Set ws = Worksheets("Sheet" & i)
               
            If List1.ListCount > 0 Then     'Greater than zero then one or more items
                For lngItem = 0 To List1.ListCount - 1
                    With ws
                        'No totals on this sheets so no need to insert rows
                        'If insert rows required then same as for sheet2.
                        Set rngDestin = .Cells(.Rows.Count, "a").End(xlUp).Offset(1)
                        rngDestin.Value = List1.List(lngItem, 0)
                        rngDestin.Offset(0, 1).Value = List1.List(lngItem, 1)  'Offset this from frist column
                        rngDestin.Offset(0, 2).Value = List1.List(lngItem, 2)  'Offset this from first column
                    End With
                Next lngItem
            End If
           
            If list2.ListCount > 0 Then     'Greater than zero then one or more items
                For lngItem = 0 To list2.ListCount - 1
                    With ws
                        'Inserts lines between column headers and totals block
                        Set rngDestin = .Cells(.Rows.Count, "a").End(xlUp).Offset(1)
                        'Next line format from below inserted line so formatting of headers is not copied
                        rngDestin.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow  'Insert a row first
                        Set rngDestin = .Cells(.Rows.Count, "a").End(xlUp).Offset(1)
                        rngDestin.Value = list2.List(lngItem, 0)
                        rngDestin.Offset(0, 1).Value = list2.List(lngItem, 1)
                        rngDestin.Offset(0, 2).Value = list2.List(lngItem, 2)
                    End With
                Next lngItem
            End If
        Next i
       
    End Sub


    Regards, OssieMac

    Sunday, November 5, 2017 6:43 AM
  • I forgot to mention in my previous post that you should avoid using object names that are reserved for the VBA code like List, Item etc.

    Also add Option Explicit at the top of all modules so that you can click on Debug -> Compile and it will check all of the code for properly dimensioned variables and valid object names. Can save a lot of problems in debugging the project where the only error is a mistyped variable or object name. Doesn't pick up all errors but it picks up a lot.

    You will need to go through this procedure after copying the code to the project and find the objects where I have renamed in the code and you will need to rename the controls in the Userform.

    You can force the system to automatically insert the Option Explicit in all new projects or newly added VBA modules as follows.

    1. Open the VBA editor
    2. Select menu item Tools -> Options
    3. Check the box against "Require variable declaration"
    4. The option remains set and does not disappear after closing Excel


    Regards, OssieMac


    • Edited by OssieMac Sunday, November 5, 2017 11:16 PM
    Sunday, November 5, 2017 6:58 AM
  • https://1drv.ms/x/s!ArTfUcJS-eVihWzdeShRn_CGFUTb

    I have a different suggestion, have a look into this file:

    https://www.dropbox.com/s/22k8yx0vvx0zwo4/47bd6353-793f-40a9-82ef-ba70c99a7008.xlsm?dl=1

    Open the file, press Alt-F8 and execute UserForm1Show

    Andreas.

    Sunday, November 5, 2017 8:26 AM
  • Thank you both OssieMac and Andreas for your help!

    I used some suggestions and code from you both, and nearly have a userform that is ready to use! All I haven't done is finish changing the object names as Ossie has suggested until i get my entire form working.

    When I replaced one of the textboxes with a combobox, i keep getting a "mismatch" error that debugs under my add2_click. I  believe I have correctly replaced the code in the add_click_prim to include one textbox and one combobox, but now i only have the use of the add1 button for my combobox field, with the error occurring when the textbox add2 button is clicked. If I can solve this, that puts me where I need to be.

    here is the updated OneDrive file to you to review and help me fix this error

    https://1drv.ms/x/s!ArTfUcJS-eVihW24yZVI8avzq-QB

    Monday, November 6, 2017 10:15 PM
  • I have an appointment to keep so have not got time to test at the moment.

    However, try converting your second textbox to a combobox then I think that it will probably work. The second parameter of the called sub is declared as a combobox (not textbox) and you are passing a textbox to it. See the second parameter in the following called sub.

    Private Sub Add_Click_Prim(ByRef List As msforms.ListBox, _
        ByRef itembox As msforms.ComboBox, ByRef PriceBox As msforms.TextBox, ByRef QuantityBox As msforms.TextBox)


    Regards, OssieMac

    • Marked as answer by dross85 Tuesday, March 13, 2018 9:08 PM
    Monday, November 6, 2017 10:56 PM