Based on selection from comboboxes of a userform Display matching row to a seperate userform

[excel_sat]

I have 2 dependent comboboxes on userform1.
Ex: If I select Name form combobox1, and Smith from combobox2 and click on "Search" button the row matching to that name should get displayed on a seperate userform say userform2 . The sheet iam pulling the data from is "Actualsdata".

How to pouplate the data on to a userform based on the selection made from combobox which are on another userform?

Also is there a way to display a message when there is no data found?

thanks in advance.

 

[Jorge_B]

Hi,
If I understand you correctly, I think you could accomplish what you need by using the Match function to return the row of coincidences found in “Actualsdata”.
Also, you´ll need to declare a Variable in a standard module to store the row of the data found, as follows:

Public ResultRow As Long 		‘If only one coincidence may be found.

Then, you can use that information (the row index) in a sub procedure in order to populate userform2. For instance:

Sub PopulateUserform2 (Row as Long)					
TextBox1 = Sheets(“Actualsdata”).Cells(Row, 1) 
…. Code to populate other controls in userform2
End Sub

And then calling it at the initialize event.

Private Sub UserForm2_Initialize()
Call PopulateUserform2 (ResultRow)
End Sub

Hope it helps.
Jorge B.

 

[excel_sat]

Thanks Jorge.. Will try this

Could you also guide me on how to display the resultant rows if there are more than one coincidences. I mean if I want all the rows that have the matching value to be displayed

 

[Jorge_B]

Hi,
If multiple coincidences may be found, you could use an dynamic array instead of a single variable:

‘In a standard module:
Public ResultsRows() As Long

I guess a good way to proceed after clicking the “search” command button is filtering data in “Actualsdata” based on the criteria selected in combobox2. That’s because after filtering you could use the speciallcells property to populate the array without looping throughout all data.
So, here’s how I think you can do it:

Sub GetRows()
Dim i As Long
Dim NumbResults As Long
Dim Msg As String
Dim Row As Range
Dim DataRng As Range
Dim FilterRng As Range
Set DataRng = Sheets("Actualsdata").UsedRange
On Error GoTo NotFound
DataRng.AutoFilter Field:=1, Criteria1:=UserForm1.ComboBox2.Value [COLOR="#008000"][B]'Adjust this line to fit your needs[/B][/COLOR]
DataRng.Rows(1).Hidden = True
NumbResults = Application.WorksheetFunction.Subtotal(103, DataRng.Columns(1).SpecialCells(xlCellTypeVisible))
ReDim ResultsRows(1 To NumbResults)
Set FilterRng = DataRng.SpecialCells(xlCellTypeVisible)
i = 1
For Each Row In FilterRng.Rows
ResultsRows(i) = Row.Row
i = i + 1
Msg = Msg & Row.Row & vbNewLine
Next
DataRng.Rows(1).Hidden = False
DataRng.AutoFilter
MsgBox Msg
Exit Sub
NotFound:
MsgBox "No coincidences found.", vbInformation
End Sub

In my previous reply I thought that you wanted to populate several controls (textbox, combobox...) in userform2. Now I’m not so sure, so I just added a MsgBox to display the number of the rows as an example.
So let me know if you want to populate controls or just display the rows numbers.

Hope it helps.

 

[excel_sat]

Hi Jorge.. You replies have been of great help.

actually i want to get all the rows matching the combobox selection to be displayed in a seperate userform (userform2).
Is there a way to do that?

Many thanks

 

[Jorge_B]

Hi,
Displaying them in userform2 is absolutely possible. Once you got the rows stored in an array there’re several ways to accomplish that.
I have a couple of questions though, which controls are placed in userform2?, Do you want to display all the rows at once or one by one (with previous-next buttons)?
Regards.

 

[excel_sat]

There are only few command buttons.. the list of rows should be displayed at once in a list view .. just like they are shown in excel sheet

 

[Jorge_B]

Hi,
I think I finally understand. You want to display the entire rows in a listbox.
You have two options then.
1. Using the RowSource property: the problem is that it has to be a contiguous range of cell, so you’d had to copy the filtered range to another worksheet (that can be deleted afterwards) in order to use it as the RowSource.
2. Using the AddItem method: It works with a filtered range, but it might be a Little lengthy with large amounts of data (too much rows and/or columns).

Here’s how to do it with the RowSource property. Let me know if you’d like to try using the AddItem method.

Click event of search button:

Private Sub SearchBtn_Click()
Me.Hide
Userform2.Show
End sub

Initialize event of Userform2

Private Sub UserForm_Initialize()
Dim DataRng As Range
Dim FilterRng As Range
Dim RowSourceRng As Range
Set DataRng = Sheets("Actualsdata").UsedRange
On Error GoTo NotFound
DataRng.AutoFilter Field:=1, Criteria1:=UserForm1.ComboBox2.Value 'Adjust this line to fit your needs
Set FilterRng = DataRng.SpecialCells(xlCellTypeVisible)
Worksheets.Add After:=Sheets(ThisWorkbook.Sheets.Count)
Sheets(ThisWorkbook.Sheets.Count).Name = "RowSource"
FilterRng.Copy Destination:=Sheets("RowSource").Range("A1")
Set RowSourceRng = Sheets("RowSource").UsedRange
Set RowSourceRng = RowSourceRng.Offset(1, 0)
Set RowSourceRng = RowSourceRng.Resize(RowSourceRng.Rows.Count - 1)
Me.ListBox1.ColumnCount = RowSourceRng.Columns.Count
Me.ListBox1.ColumnHeads = True
Me.ListBox1.RowSource = RowSourceRng.Address
Exit Sub
NotFound:
MsgBox "No coincidences found.", vbInformation
End Sub

Hope it helps.