UserForm ListBox with 2D Array and Match

[SeanMathew]

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:

Sub MRReset

With MRForm
.ComboBox1.Value = ""
.ComboBox2.Value = ""
.ComboBox3.Value = ""
.ComboBox4.Value = ""
.TextBox1.Value = ""
.ComboBox6.Value = ""
.ComboBox7.Value = ""
.TextBox2.Value = ""
.ComboBox9.Value = ""
.ComboBox10.Value = ""
.TextBox3.Value = ""
.MRRowNumber.Value = ""
End With

sn = Sheets("DataTable").Range("A1:O1000").Value
sp = Application.Index(sn, [Row(1:999)], Array(1, 7, 15))
'Referenced from a third party with original source: VBA for smarties: Arrays, Title 6.7.1

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Are you project IDs numbers or text?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: UserForm ListBox with Array & Match - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

How about

Sub MRReset()
   Dim Rws As Variant, Ary As Variant
   Dim Id As String
   

With MRForm
    .ComboBox1.Value = ""
    .ComboBox2.Value = ""
    .ComboBox3.Value = ""
    .ComboBox4.Value = ""
    .TextBox1.Value = ""
    .ComboBox6.Value = ""
    .ComboBox7.Value = ""
    .TextBox2.Value = ""
    .ComboBox9.Value = ""
    .ComboBox10.Value = ""
    .TextBox3.Value = ""
    .MRRowNumber.Value = ""
End With

   Id = Sheets("controls").Range("B1")
   With Sheets("Datatable").Range("A2:O1000")
      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))
   End With

MRForm.ListBox1.List = Ary

  
End Sub

 

[SeanMathew]

I got your note on OzGrid as well. I'll make sure to mention cross posts between site forums in the future. These are the only two forums I posted to.

Project IDs will be text format.
I'll this code out shortly and provide some feedback.

FYI Go England Rugby! I play for a local men's club in the USA so very familiar with the sport.

 

[Fluff]

Ok, let us know how it goes.

 

[SeanMathew]

Ok, let us know how it goes.

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/

 

[SeanMathew]

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/

Cross post correction: As posted on UserForm ListBox with Array & Match - OzGrid Free Excel/VBA Help Forum

 

[Fluff]

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?

 

[SeanMathew]

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 UserForm ListBox with Array & Match - OzGrid Free Excel/VBA Help Forum