User Form to Find Row With Data from More than One Column 2

[shelby55]

Hello

I would like to create a dialog box or user form to enter in data and then a button so the user is taken to the row matching the input.

The input is a combination of two cells - CMG and RIL which are text fields. I can build the user form but not able to create code that sends the user to the row matching the input criteria of the user form.

Any assistance appreciated - thanks.

 

[Andrzejek]

If that's Excel, what's wrong with:
Data - Filter - Choose from column CMG - Choose from column RIL

No code.

Have fun.

---- Andy

 

[shelby55]

Hi

I wanted to have a user form for each of use. Is it possible?

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

I found code from the Excel Hackers Book which allowed me to build the user form but they had the selection of 3 fields (and I couldn't figure out how to not use that 3rd field and still work). Plus that version had where the "Find" button would display the range(s) meeting the 3 field criteria in a list box on the userform and the user would double click that to take them to the row. I think that is extra steps I don't need and want to convert that to just having the Find button go to the matching row.

I also couldn't figure out how to get the combo box on the form to be sourced by the list itself for CMG and RIL. Right now the combo boxes are frozen and won't allow entry so I can't even get it to the point where the range shows up in the list box.

Code for first combo box:

Private Sub ComboBox1_Change()
'Pass chosen value to String variable strFind1
strFind1 = ComboBox1
'Enable ComboBox2 only if value is chosen
ComboBox2.Enabled = Not strFind1 = vbNullString

End Sub

Code for the "Find Button":

Private Sub cmdFind_Click()
Dim lCount As Long
Dim lOccur As Long
Dim rCell As Range
Dim rcell2 As Range
Dim bfound As Boolean

'At least one value from combobox1 must be chosen
If strFind1 & strFind2 = vbNullString Then
MsgBox "No items to find chosen", vbCritical
Exit Sub 'Go no further
ElseIf strFind1 = vbNullString Then
MsgBox "A value from " & Label1.Caption _
    & " must be chosen", vbCritical
   Exit Sub 'go no further
End If

'Clear any old entries
On Error Resume Next
ListBox1.Clear

On Error GoTo 0

'if string variable are empty pass the wildcard character

If strFind2 = vbNullString Then strFind2 = "*"



'set range variable to first cell in table
Set rCell = rRange.Cells(1, 1)
'Pass the number of times strFind1 occurs
lOccur = WorksheetFunction.CountIf(rRange.Columns(1), strFind1)

'loop only as many times as strFind1 occurs
For lCount = 1 To lOccur
'set the range variable to the found cell.  This is then also _
used to start the next find from (After:=xCell)
Set rCell = rRange.Columns(1).Find(what:=strFind1, After:=rCell, _
LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'Check each find to see if strFind 2 occurs on the same row
If rCell(1, 3) Like strFind2 Then bfound = True 'used to not show message box for no value found
ListBox1.AddItem rCell.Address & ":" & rCell(1, 3).Address
'End If

Next lCount

If bfound = False Then  'no match
MsgBox "Sorry, no matches", vbOKOnly

End If
End Sub

Code for when Form is initialized:

Private Sub UserForm_Initialize()
'procedure level module
Dim lRows As Long


'set module level range variable to current region

Set rRange = Selection.CurrentRegion

If rRange.Rows.Count < 2 Then 'only 1 row
MsgBox "Please select any cell in your table first", vbCritical
Unload Me 'Close Userform
Exit Sub
Else

With rRange

'set Label to the table headings
Label1.Caption = .Cells(1, 1)
Label2.Caption = .Cells(1, 3)


'set rowsource of combobox to the approproate columns inside the table
ComboBox1.RowSource = .Columns(1).Offset(1, 0).Address
ComboBox2.RowSource = .Columns(3).Offset(1, 0).Address

End With
End If
End Sub

Thanks for any and all assistance.

 

[PHV]

rRange is not defined in cmdFind_Click.
Hint; use Option Explicit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Thanks PHV but module level variables are indicated at the top of the module with Option Explicit.

Any other assistance you can give? Thanks.

 

[SkipVought]

So is this one of those sitruations that when you select a value from CMG, that you would expect the list for RIL to contain only those values associated with the selection value from CGM?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hey Skip!

Yes..and no. There are 6 RILs to every CMG so if I just populated the CMG combo box with the CMGs from the worksheet itself, each would show up 6 times. So I was thinking of having a hidden worksheet with each CMG noted only once and that is the source of the combo box.

Please note that this is a list that will never change i.e. data isn't being entered, only looked up so the source for each combo box won't change. At this point, it isn't even my top priority i.e. if each box is blank and the user has to enter in the data which sends them to the correct row, I'm fine with that.

Thanks.

 

[SkipVought]

You need a unique list of CMGs for the first combo.

Then you need a 2 column list if each RIL and correcponding CGM.

So you could do a query on the 2-column table given a selection in the first combo to get the row source for the second combo

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip....but I still don't have the code for the "find button" which will take the user to that row.

 

[SkipVought]

okay, here's a solution, once you've selected the 2 values.

Sub testSelectRow(sCMG As String, sRIL As String)
    Dim lRow As Long
    
    With ActiveSheet.UsedRange
        .AutoFilter _
            Field:=Rows(1).Find("CMG").Column, _
            Criteria1:=sCMG
        .AutoFilter _
            Field:=Rows(1).Find("RIL").Column, _
            Criteria1:=sRIL
            
        lRow = Intersect(.Cells, Range(Cells(2, 1), Cells(Rows.Count, 1)).EntireRow).SpecialCells(xlCellTypeVisible).Row
    End With
    
    ActiveSheet.ShowAllData
    
    Rows(lRow).Select
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip...I'll give it a go!

 

[shelby55]

Hi Skip

Sorry but I'm still blanked on this. If I'm using a User Form to get the CMG and RIL information, how do I pass those two fields to above? Is above supposed to be run from the "RUN" or "OK" button on my user form?

Note also that I used the above and added code to set the values for CMG and RIL and when I ran it, it didn't work. I don't know if I'm interpreting the code correctly but is it supposed to filter on the criteria and then only that line of data is showing?

Thanks.

 

[SkipVought]

yes it could run from a button.

Each control has a Value property. You might call it from your button_click event

Sub YourButtonName_Click()
  testSelectRow(CMG.value, RIL.value)
end sub

assuming that your two controls are named CMG & RIL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry but this still isn't working.

The table has 7 columns of information. CMG is in column 1 and RIL is in column 3, they both have the column name of CMG and RIL.

I've put the following code in a module:

Sub SelectRow(sCMG As String, sRIL As String)
    Dim lRow As Long
    
    With ActiveSheet.UsedRange
        .AutoFilter _
            Field:=Rows(1).Find("CMG").Column, _
            Criteria1:=sCMG
        .AutoFilter _
            Field:=Rows(1).Find("RIL").Column, _
            Criteria1:=sRIL
            
        lRow = Intersect(.Cells, Range(Cells(2, 1), Cells(Rows.Count, 1)).EntireRow).SpecialCells(xlCellTypeVisible).Row
    End With
    
    ActiveSheet.ShowAllData
    
    Rows(lRow).Select
End Sub

In the user form I have a combo box for CMG and one for RIL (named CMG and RIL). I also have a command button where I have the code:

Private Sub cmdFind_Click()
  SelectRow(CMG.value, RIL.value)
End Sub

But I'm getting the message "compile error: expected:=". What am I doing wrong? Thanks.

 

[PHV]

[!]Call [/!]SelectRow(CMG.value, RIL.value)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Private Sub cmdFind_Click()
  SelectRow CMG.value, RIL.value
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

No problem Skip, I always assume it's me. I changed the code and I get a run-time error 1004 indicating no cells found. It stopped on the line of code below.

lRow = Intersect(.Cells, Range(Cells(2, 1), Cells(Rows.Count, 1))

CMG is in column A and RIL is in column C, does that have something to do with it? OR am I supposed to be creating a column where two fields are concatenated?

Thanks.