Excel Userform - Advanced Search and Filter Functionality

Hello,

I've been working on an Excel spreadsheet that will act as a parking permit database. It will contain several thousand entries, so the requirement is for there to be a user form, from which new records can be added, or existing records searched for and edited or deleted.

To help me with this, I found a very useful series of guides here: http://www.onlinepclearning.com/edit...om-a-userform/

Following that guide I was able to correctly set up the example spreadsheet they provide and get it working correctly.

I then created my own spreadsheet, based on this - although it will contain a lot more data.

Most functionality is working except the search function from within the Userform.

From what I can tell, the search function within the userform does not pull over the data from columns B5:AJ5 to AR5:BZ5 as it does when using the Macro on Sheet2 (In Sheet2 the search term is input into cell AP6, then Macro is activated by pressing Ctrl+Shift+A). The data that is copied is then picked up by the 'outdata' named range.

If working correctly I can import all the data, or a matched search, and have it display on the list box within the form.

I can then edit or delete the data within the form.

I've trawled through the code for some time, but can't identify what is missing! Any help offered would be much appreciated.

The spreadsheet is attached.

Thanks for your help. Hope the above is clear, but let me know if I can provide further info.

Kieron

This will populate the listbox

Private Sub cmdContact_Click()
'dim the variables
Dim Crit As Range, FindMe As Range, DataSH As Worksheet, lr1 As Long, mycol As Integer
'set object variables
Set DataSH = Sheet2
'hold in memory and stop screen flicker
Application.ScreenUpdating = False

lr1 = DataSH.Range("B" & Rows.Count).End(xlUp).Row
DataSH.Range("AR6", "BZ" & lr1).ClearContents

If Me.txtSearch = "" Then
    DataSH.Range("B6", "AL" & lr1).Copy DataSH.Range("AR6")
    lstStaff.RowSource = DataSH.Range("outdata").Address(external:=True)
Else
    'if all columns is selected
    If Me.cboHeader.Value = "All_Columns" Then
        Set FindMe = DataSH.Range("B6:AJ10005").Find(What:=txtSearch, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False):
        DataSH.Range("A5", "AL" & lr1).AutoFilter FindMe.Column, Criteria1:=FindMe.Value
        With DataSH.Range("B5").CurrentRegion
            .Offset(1).Copy DataSH.Range("AR6")
        End With
        DataSH.AutoFilterMode = False
        lstStaff.RowSource = DataSH.Range("outdata").Address(external:=True)
    End If
    'if not all columns is selected
    If Me.cboHeader.Value <> "All_Columns" Then
        If cboHeader = "Last_Name" Then mycol = 3
        If cboHeader = "Email_Address" Then mycol = 10
        If cboHeader = "Work_Tel_No" Then mycol = 11
        If cboHeader = "Mob_Tel_No" Then mycol = 12
        If cboHeader = "Reg1_No" Then mycol = 18
        If cboHeader = "Reg2_No" Then mycol = 24
        DataSH.Range("A5", "AL" & lr1).AutoFilter mycol, Criteria1:=txtSearch
        With DataSH.Range("B5").CurrentRegion
            .Offset(1).Copy DataSH.Range("AR6")
        End With
        DataSH.AutoFilterMode = False
        lstStaff.RowSource = DataSH.Range("outdata").Address(external:=True)
    End If
End If
End Sub

Kind regards
Leo

Hi Leo,

Thank you for your response. This code works perfectly!

Best wishes

Kieron

Hi Kieron,

glad i could help, is there a reason why the status of case is not Solved ?
if you forgot, please do now

Kind regards
Leo

I've just marked the thread as Solved!

Thanks

Kieron