UserForm ListBox with 2D Array and Match

SeanMathew

New Member
Joined
Jan 14, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Are you project IDs numbers or text?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Jan 14, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
Ok, let us know how it goes.
 

SeanMathew

New Member
Joined
Jan 14, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 14, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 14, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,970
Messages
5,656,164
Members
418,284
Latest member
LaurentT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top