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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
You don't need to mention the cross post in every thread, just when you start the thread. ;)

Because the listbox is getting filtered you will need to loop through the data to find the correct record.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

SeanMathew

New Member
Joined
Jan 14, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
With regards to the code provided for the listbox, can you offer a little explanation. I'm learning all of this through online resources so I'm not familiar with the expressions being used. I defined the Chr(34) through ASCII but what are the @ and # expressions? More importantly, how are they being defined?

My issue with looping through the data to find the correct record is that there may not be a distinguishing variable. Multiple rows can have the same variables within a given project ID. Can the loop be set to find the correct record based on the row number?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
Would you be willing to have an extra column that contains the row number?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok I have added the formula
Excel Formula:
=ROW()
in P2 copied down & then in the Reset code use
VBA 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
VBA 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
 
Solution

SeanMathew

New Member
Joined
Jan 14, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Works perfectly, thank you!

Can you offer a brief explanation of the @ and # expressions, or a good source to review for this?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Both the @ & # signs are just place holders & get replaced by the bits at the end of the code, so you end up with a formula like
Excel Formula:
if(.Columns(1).Address=" & Chr(34) & Id & Chr(34) & ",row(.Columns(1).Address)-.Rows(0).Row,false)
which roughly equates to
Excel Formula:
if(A2=Id,row(A2)-1,false)
although the code looks at every row in "one hit" rather than looping through them.
 

SeanMathew

New Member
Joined
Jan 14, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Both the @ & # signs are just place holders & get replaced by the bits at the end of the code, so you end up with a formula like
Excel Formula:
if(.Columns(1).Address=" & Chr(34) & Id & Chr(34) & ",row(.Columns(1).Address)-.Rows(0).Row,false)
which roughly equates to
Excel Formula:
if(A2=Id,row(A2)-1,false)
although the code looks at every row in "one hit" rather than looping through them.
Thank you Fluff! I really appreciate all of your help solving this issue.

I was holding off on responding because I was trying to implement this code on another form in the same workbook. Initially I had an issue but I figured it out (there were some different references that needed to be changed).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,973
Messages
5,656,178
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