UserForm ListBox with Array & Match

    [xpost][/xpost]


    Relatively new with VBA and doing my best to learn it. I have a userform which has a listbox. To this I would like to add some type of Match function so that the listbox only displays the rows in which a certain value is displayed. For example, the project ID is displayed in Sheet1 cell B1. In the table, the corresponding project IDs are in Sheet2 column A. I would like the listbox to only show the rows in which the value in Sheet 2 Column A match the value in Sheet 1 cell B1.


    Currently the listbox displays all of the rows from the sourced table with only select columns. How can I modify this to include the Match function as described above. Open to other methods as well (i.e. filter, etc).


    Current Code:

    Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.

    ['code]


    your code goes between these tags


    ['/code]


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

    You need to attach an example workbook. If you search my recent posts you will find several examples of UserForms with ListBoxes displaying specific rows

    Thank you royUK for adding the code tags. I read up on the forum rules a bit more after you posted.


    I found a few examples of displaying specific rows but each one has had a "twist" in the approach and I could not figure out how to implement with my code.


    I am attaching an example workbook as requested. Essentially, for whichever project ID is selected on the "Controls" sheet, I'd like the Material Reservation UserForm to display only those records in the listbox.


    Any guidance would be greatly appreciated. Thank you.

    Quote from SeanMathew

    I read up on the forum rules a bit more after you posted.

    Obviously not very well as you haven't mentioned your cross posting. ;)


    as posted on MrExcel https://www.mrexcel.com/board/…-array-and-match.1168920/

    Fluff13 I did see the cross post comment but I thought that meant another Forum within OzGrid. I'll make sure to include it for other site forums in the future.


    I'm new here, give me a break lol. I'll get it down.


    As noted on the other forum, I'll give this code a try shortly and provide some feedback. Thank you for your help!

    I need to work with this a bit more to better understand the code provided. I'm trying to learn as I go with this and I'd like to work through the code myself a bit. However, if I can't figure something out I may ask for further explanation.


    On first attempt, the functionality seems to work great. However, the only issue I have is that I cannot select the first row in the listbox. I switched the list property .ColumnHeads to True and I regained selection functionality for all of the records but the headers are blank.


    I tried changing the Range to .Range("A1:O1000") to include the column headers but now I get a Run-time error '1004': Application-defined or object-defined error.


    Any thoughts on this?


    as posted on MrExcel https://www.mrexcel.com/board/…-array-and-match.1168920/

    Fluff13 said:

    With that code you cannot populate a header row in the listbox, however you should be able to select the first row in the listbox.

    Are you having this problem with your sample file?


    I was having the same issue with the sample file. However the row selection in the listbox is being controlled with Function MRSelected_List so I just modified that from MRSelected_List = i to MRSelected_List = i+1 and I was able to get back functionality to select all rows within the listbox. I can add Column labels to address the header issue.


    With some additional testing this morning, I did find one other issue. Try selecting MD6 and then click "Edit Record". The values populating the form are for record MD4.


    Because the selected row value in the listbox is not equal to the row number in the source DataTable, when the Edit Record button is clicked, the values brought back to the form are incorrect. For listbox entries MD1, MD2, and MD3 it looks okay because the rows are sequential; however, for MD6 the array is skipping two rows containing other project IDs.


    Is there an easy way to correct this or do I need to have a filtered array (like you provided for the listbox) to set the row value properly?


    As cross post https://www.mrexcel.com/board/…tch.1168920/#post-5680096

    Ok I have added the formula

    =ROW()

    in P2 copied down & then in the Reset code us


    Code
        Id = Sheets("controls").Range("B1")
   With Sheets("Datatable").Range("A2:P1000")
      Rws = Filter(.Worksheet.Evaluate(Replace(Replace("transpose(if(@=" & Chr(34) & Id & Chr(34) & ",row(@)-#,false))", "@", .Columns(1).Address), "#", .Rows(0).Row)), False, False)
      ReDim Preserve Rws(UBound(Rws) + 1)
      Ary = Application.Index(.Value, Application.Transpose(Rws), Array(1, 7, 15, 16))
   End With

    and n the edit button click use

    Code
    With Me.ListBox1
   If .ListIndex = -1 Then
       MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"
       Exit Sub
   End If
   Me.MRRowNumber.Value = .List(.ListIndex, 3)
End With

'Code to update the values to respective controls
Dim sht1 As Worksheet

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login