Posts by mycomputerguy-w

    Re: Error handling for ListBox1.ColumnCount = UBound(vData, 2)


    Well..... you got closer than I did but there are still a couple things, first, the list box needs to be multiple columns. In the testbook case, 5 columns. Then I need to check the text in the combobox against ALL the text that is and will be in the dynamic range. If what is typed its not in the range, open pop up to ask if the user wants to add the data, with a command button or the vbYesNo, open another userform to add the data to the appropriate columns. I think can figure out the pop up box and the userform to add part but I'm not very good at the VBA for the rest, so I'm struggling a bit to figure that part out.

    Re: Error handling for ListBox1.ColumnCount = UBound(vData, 2)


    If you download the testbook, and run the userform, you'll see that it has a combobox that is populated by the dynamic range od $A$A. if you select anything within the combobox, it will list the appropriate data in the rows of the list box on the same userform. In any case.... if you select an entry in the combobox, it works great. The problem is this, if you try to type in a number that IS NOT in the list within the combobox, that's when it throws the error. for example: if you type in "234" you'll get 2 lines of data in the listbox, if you type in "345" you should get 3 lines of data in the listbox, if you even begin to type something like "805" you wont even get to the 5 before it throws the error. just by typing the "8" throws the error.


    That's what I'm trying to fix. Id rather it pop a message box asking to add the data then to error out. the actual error is on the line....."ListBox1.ColumnCount = UBound(vData, 2)"



    Hope this explains it a little better.


    I've included another copy of the file in case the first one didn't work right.

    I have another post at http://www.ozgrid.com/forum/showthread.php?t=198604 in which the code below is used to list information In a listbox on a userform. The code works great as long as you ONLY use a number that is already in the dynamic list in the combobox. If you even begin to type a number that is not in the dynamic list I get a "subscript out of range" error on the line: "ListBox1.ColumnCount = UBound(vData, 2)".


    If someone can take a look at it and see if it can be figured out, that'd be great. What I'm kind of thinking is that an error handler can be made to handle the error by popping up a message box that says the Customer ID was not found and will ask if the user would like add a new user with a vbYesNo option. yes opens a second userform to add the user, no exits the script.




    Ive added the testbook that goes with the code.


    I look forward to the possible answers.

    Re: Find exact match(s) and display select cells of the row in a list box


    Hello FuzzHead, I encounterd a problem that I didn't see coming and I'm sure you didn't either. I constructed a testbook with the above code and it works great, EXCEPT if you try to add data.


    If you are able to download the testbook and look at it, you'll see what I mean. to explain, as long as the data is already in the worksheet, in the dynamic range, it will list it in the combobox and when you select it in the combobox, it shows in the listbox just like it should. If the data is not in the workbook and needs to be added, OR if you even start to type a number that isn't in the dynamic range I get an error on the following line,


    SubScript out of range


    "ListBox1.ColumnCount = UBound(vData, 2)"


    Any Ideas??

    Re: Find exact match(s) and display select cells of the row in a list box


    Based on the table above, if the user chooses "234" in the combobox that is already there, the data that I want to show up in the listbox is as follows:


    (line1) | 234 | 1 | 6 | 6 | 4 |
    (line2) | 234 | 6 | 3 | 3 | 1 |
    (line3) | 234 | 7 | 7 | 2 | 2 |


    3 lines of data in the listbox because "234" occurs 3 times in the table.


    If the user chooses "456" then id expect the listbox to show


    (line1) | 456 | 4 | 1 | 1 | 7 |


    only the one line of data simply because "456" only occurs once in the table
    and so on........


    I hope this explains it better.

    Re: Find exact match(s) and display select cells of the row in a list box


    No, I don't want to hide the row data, I want to use the data that is found in a list box on a userform. The problem that I keep running into is that I need to look at a dynamic range for the value that is chosen in the combobox by the user. Once the data is found, I need to pass it to the listbox on the userform. The data in the first column could contain 1 instance or up to 20 or more instances.... the data in the first column is the dynamic range that fills the combobox that the user chooses from.


    By looking at your code, I'm guessing that you really didn't understand what I was asking but Thank You for trying.

    Im not sure if this is even possible but i'll give it a try.


    Is there a way in VBA to code to find an exact match(s) based on the value of a combobox on a userform(user selects the value of the combobox), then based on that value, find an exact match or matches that may occur in the same column? If this much is possible then can VBA be coded to look at all the rows where the match or matches occured, and list selected data from other cells IN THE SAME ROW in a listbox?


    Based on the table below: The user chooses "234" in the combobox dropdown.


    At that point, Id like the listbox to only show the duplicates of the 3 rows and 5 columns where "234" occured in the first column.


    Possible?? I guess we'll see. :)


    [TABLE="class: grid, width: 300"]

    [tr]


    [td]

    234

    [/td]


    [td]

    1

    [/td]


    [td]

    6

    [/td]


    [td]

    6

    [/td]


    [td]

    4

    [/td]


    [/tr]


    [tr]


    [td]

    345

    [/td]


    [td]

    2

    [/td]


    [td]

    6

    [/td]


    [td]

    3

    [/td]


    [td]

    9

    [/td]


    [/tr]


    [tr]


    [td]

    456

    [/td]


    [td]

    4

    [/td]


    [td]

    1

    [/td]


    [td]

    1

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    678

    [/td]


    [td]

    5

    [/td]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [td]

    8

    [/td]


    [/tr]


    [tr]


    [td]

    234

    [/td]


    [td]

    6

    [/td]


    [td]

    3

    [/td]


    [td]

    3

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    234

    [/td]


    [td]

    7

    [/td]


    [td]

    7

    [/td]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: Use INDEX/MATCH in VBA to fill labels


    Just so that anyone coming to this thread for help will know, I did figure out the sort routine.


    The following code needs to go into an initialize_Userform sub as shown below and it sorts like a champ!!


    Thanks for all your help!!


    Code
    Private Sub UserForm_Initialize()
            
        Sheets("WorkOrders").Range("B1").Sort Key1:=Sheets("WorkOrders").Range("B1"), Order1:= _
                xlDescending, Header:=xlGuess, OrderCustom:=6, _
                MatchCase:=False, Orientation:=xlTopToBottom
         
    End Sub

    Re: Use INDEX/MATCH in VBA to fill labels


    I did that and that works........so here is my question, is there VBA that will allow me insert a blank row at the top when I enter the data using the userform rather than adding it to the bottom? That way the data will already be in the order I need.

    Re: Use INDEX/MATCH in VBA to fill labels


    First of all... Thank you Max1616.... your routine DOES work. It does what I need it to do. However, once I get to more than 5 entries for each custnum, then it will only show me all the same info. So if you can, tell me how to tweak the code to make it sort largest number to smallest (In column B). Right now it sorts in the opposite direction. If I can sort it from largest workorder num to smallest, then it will be perfect.