Update Values in Userform when filter is changed

Hi folks,

here's my new dilema!

I have a userform which has 4 criteria at the top that filters data.

from that data i get a reference number and that is automatically placed into a cell.

that is then used to do a Vlookup and these lookups then complete the userform textboxes.

however if i change the filter it doesn't update the textboxes?

here's my code:

Private Sub CmdFilter_Click()

Dim Sprod As String
If FrmNew.CBProduct.Value = "Electricity" Then
Sprod = "E"
ElseIf FrmNew.CBProduct.Value = "Gas" Then
Sprod = "G"
End If

Application.ScreenUpdating = True
'select worksheet dependant on account type
    If FrmNew.CBAccType.Value = "Touched" Then
        Worksheets("Touched Work").Select
    ElseIf FrmNew.CBAccType.Value = "Dayfiled" Then
        Worksheets("Dayfiled").Select
    End If
'filter data by product
    If FrmNew.CBProduct.Value = "Electricity" Then
        Selection.AutoFilter Field:=13, Criteria1:="E"
    ElseIf FrmNew.CBProduct.Value = "Gas" Then
        Selection.AutoFilter Field:=13, Criteria1:="G"
    End If
'filter remaining data by age
    If FrmNew.CBAccAge.Value = "Oldest" Then
    Range("AT1").Select
    Range("A1:CN7707").Sort Key1:=Range("AT1"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ElseIf FrmNew.CBAccAge.Value = "Newest" Then
    Range("AT1").Select
    Range("A1:CN7707").Sort Key1:=Range("AT1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End If
    
'filter by billing type
    If FrmNew.CBBilling.Value = "Monthly" Then
        Selection.AutoFilter Field:=33, Criteria1:="Monthly"
    ElseIf FrmNew.CBBilling.Value = "Quarterly" Then
        Selection.AutoFilter Field:=33, Criteria1:="Quarterly"
    End If
    
'select which sheet to pull the data from
    If FrmNew.CBAccType.Value = "Touched" Then
        Worksheets("Touched work").Select
    ElseIf FrmNew.CBAccType.Value = "Dayfiled" Then
        Worksheets("Dayfiled").Select
    End If
    
    Range("E2").Select
        Do Until ActiveCell.Offset(0, 8).Value = Sprod
        ActiveCell.Offset(1, 0).Select
        Loop
        Application.ScreenUpdating = True
            Worksheets("Cover").Range("A6").Value = ActiveCell.Offset(0, 3).Value 'Bill Status
            Worksheets("Cover").Range("A8").Value = ActiveCell.Offset(0, 1).Value 'New Flag
            Worksheets("Cover").Range("A10").Value = ActiveCell.Offset(0, 65).Value ' Port Rec
            Worksheets("Cover").Range("A12").Value = ActiveCell.Offset(0, 68).Value ' Unbilled Value
            Worksheets("Cover").Range("A14").Value = ActiveCell.Offset(0, 20).Value ' Company Name
            Worksheets("Cover").Range("A16").Value = ActiveCell.Offset(0, 66).Value ' Unbilled Reason
            Worksheets("Cover").Range("A4").Value = ActiveCell.Value
'add the data into the userform
            Worksheets("Cover").Range("A2").Value = FrmNew.CBAccType.Value
            
            FrmNew.TxtCRN.Value = Worksheets("Cover").Range("A4").Value
            FrmNew.TxtBillStatus.Value = Worksheets("Cover").Range("A6").Value
            FrmNew.TxtFlag.Value = Worksheets("Cover").Range("A8").Value
            FrmNew.TxtPRec.Value = Worksheets("Cover").Range("A10").Value
            FrmNew.TxtUnVal.Value = Worksheets("Cover").Range("A12").Value
            FrmNew.TxtCname.Value = Worksheets("Cover").Range("A14").Value
            FrmNew.TxtReason.Value = Worksheets("Cover").Range("A16").Value

        Worksheets("Cover").Activate
End Sub

Private Sub UserForm_Initialize()

FrmNew.CBAccType.AddItem "Touched"
FrmNew.CBAccType.AddItem "Dayfiled"

FrmNew.CBProduct.AddItem "Electricity"
FrmNew.CBProduct.AddItem "Gas"

FrmNew.CBAccAge.AddItem "Oldest"
FrmNew.CBAccAge.AddItem "Newest"

FrmNew.CBRoot.AddItem "BER Amendment"
FrmNew.CBRoot.AddItem "Cross License Tranfer"
FrmNew.CBRoot.AddItem "Crossed MTR"
FrmNew.CBRoot.AddItem "Datafix"
FrmNew.CBRoot.AddItem "De-aggregation Issue"
FrmNew.CBRoot.AddItem "Demolition"
FrmNew.CBRoot.AddItem "Duplicate"
FrmNew.CBRoot.AddItem "EP Mismatch"
FrmNew.CBRoot.AddItem "ET"
FrmNew.CBRoot.AddItem "GUTO Issue"
FrmNew.CBRoot.AddItem "Isolation Issue"
FrmNew.CBRoot.AddItem "Late Set Up"
FrmNew.CBRoot.AddItem "Metering issue/Dispute"
FrmNew.CBRoot.AddItem "Migration Issue"
FrmNew.CBRoot.AddItem "New Connection"
FrmNew.CBRoot.AddItem "Portfolio Rec"
FrmNew.CBRoot.AddItem "Registration Issue"
FrmNew.CBRoot.AddItem "Script Errors"
FrmNew.CBRoot.AddItem "Split Accounts"
FrmNew.CBRoot.AddItem "Unoccupied Account"
FrmNew.CBRoot.AddItem "Unsupported Meter"

FrmNew.CBBilling.AddItem "Monthly"
FrmNew.CBBilling.AddItem "Quarterly"

Dim lpbuff As String * 25
Dim ret As Long
Dim StrName As String
Dim StrSheet As String
StrSheet = FrmNew.CBAccType.Value
ret = GetUserName(lpbuff, 25)
StrName = Left(lpbuff, InStr(lpbuff, Chr(0)) - 1)
FrmNew.TxtKID.Value = StrName

End Sub

Thanks for reading my thread and thanks to that that help or at least try to