VBA Search from userform

[MrJames]

Hi all,

Could do with some assistance please as I'm a little stuck. I've created a userform to input data, now I need to know the code for searching column B and / or column H using two combo boxes. Column B is "The area" and column H is "the status".

I need this new form to then display the results from those rows that match. So, if a search was completed against column B for "Email" and there were 3 rows where column B contained email, I want the userform to display those 3 rows, in a grid like format on the form.

Any help would be greatly appreciated

 

[mikerickson]

Is there a reason why a Useform is preferable to using AutoFilter on the sheet itself?

 

[MrJames]

Hi,

Yes, I want to keep this user friendly throughout and the idea is to use userforms to input (already created) and then also to search and edit... basically so the user doesn't use the actual sheet, but uses userforms.

Kindest regards,

James.

 

[mikerickson]

User friendly?

Everyone knows how to use Excel. Given a spreadsheet with column headers Name, Address, City, State and Zip any one can figure out where to put the data.

But some folks prefer to put their interface between the user and the common Excel worksheet.

Have you tried using AutoFilter to isolate the rows in question and then load the visible cells into a multi-column listbox.

Private Sub ComboBox1_Change()
    Dim dataRange As Range
    Dim oneCell As Range
    Dim colNum As Long
    Set dataRange = Sheet1.Range("A1:F20"): Rem test range

    If ComboBox1.ListCount <> 1 Then
        dataRange.AutoFilter Field:=2, Criteria1:=ComboBox1.Text
        With ListBox1
            .Clear
            .ColumnCount = dataRange.Columns.Count
            For Each oneCell In dataRange.Resize(dataRange.Rows.Count - 1, 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
                .AddItem Application.Intersect(dataRange.Columns(1), oneCell.EntireRow)
                For colNum = 2 To dataRange.Columns.Count
                    .List(.ListCount - 1, colNum - 1) = Application.Intersect(dataRange.Columns(colNum), oneCell.EntireRow)
                Next colNum
            Next oneCell
        End With
    End If
End Sub

 

[swat45]

User friendly?

Everyone knows how to use Excel. Given a spreadsheet with column headers Name, Address, City, State and Zip any one can figure out where to put the data.

But some folks prefer to put their interface between the user and the common Excel worksheet.

Have you tried using AutoFilter to isolate the rows in question and then load the visible cells into a multi-column listbox.

Private Sub ComboBox1_Change()
    Dim dataRange As Range
    Dim oneCell As Range
    Dim colNum As Long
    Set dataRange = Sheet1.Range("A1:F20"): Rem test range

    If ComboBox1.ListCount <> 1 Then
        dataRange.AutoFilter Field:=2, Criteria1:=ComboBox1.Text
        With ListBox1
            .Clear
            .ColumnCount = dataRange.Columns.Count
            For Each oneCell In dataRange.Resize(dataRange.Rows.Count - 1, 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
                .AddItem Application.Intersect(dataRange.Columns(1), oneCell.EntireRow)
                For colNum = 2 To dataRange.Columns.Count
                    .List(.ListCount - 1, colNum - 1) = Application.Intersect(dataRange.Columns(colNum), oneCell.EntireRow)
                Next colNum
            Next oneCell
        End With
    End If
End Sub

Hi,

I am using the above code to in my userform search.

Background of my project:

Excelworkbook has 2 sheets. Sheet 1 and Sheet 2. I have a command button on Sheet 2, when clicked opens a userform for data entry. All the data is stored in Sheet 1. I have created a search button on the userform that returns the results in the Listbox as suggested above. If I run the macro through the command button on sheet 2 for search, its giving me a runtime error '1004' Resize failed.... But When I run the macro from the code itself, its works just fine. Can you please let me know what I am doing wrong?