ListView Filter

[Rex2024]

I am attempting to filter my listview upon populating based on an employee number. I can see the value is being retained, it's an employee number, but the system doesn't return anything.

Sub UserForm_Activate()

Dim p As Long
Dim CLN As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("EventLog")
Dim x As Integer
Dim RNG As Variant
Dim C As Integer

EmpNum = EventLog.EmpNum.Value

With Me.EventHistoryLV

    .ListItems.Clear
    .ColumnHeaders.Clear
    .FullRowSelect = True

    .ColumnHeaders.Add , , "Event ID", 20
    .ColumnHeaders.Add , , "EmpNum", 50
    .ColumnHeaders.Add , , "LastName", 80
    .ColumnHeaders.Add , , "FirstName", 80
    .ColumnHeaders.Add , , "Details", 250
    .ColumnHeaders.Add , , "Update Time", 120

    For p = 1 To ws.Range("A1000000").End(xlUp).Row
        If ws.Range("B" & p).Value = EmpNum Then
        .ListItems.Add , , ws.Cells(p + 1, "A")
        CLN = CLN + 1
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "B") '1
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "C") '2
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "D") '3
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "E") '4
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "F") '5
        End If
    Next p
    
End With

End Sub

 

[James006]

Hi,
Have you tried to use Private Sub UserForm_Initialize()

instead of Private Sub UserForm_Activate() ...?

 

[Rex2024]

Hi,
Have you tried to use Private Sub UserForm_Initialize()

instead of Private Sub UserForm_Activate() ...?

Just tried it. Did not make a difference.

 

[James006]

OK....
Could you test following modification for your loop instruction with :
For p = 1 To ws.Cells(Rows.Count, "A").End(xlUp).Row

 

[Rex2024]

OK....
Could you test following modification for your loop instruction with :
For p = 1 To ws.Cells(Rows.Count, "A").End(xlUp).Row

No change. Here is the modified code:

Sub UserForm_Initialize()

Dim p As Long
Dim CLN As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("EventLog")
Dim x As Integer
Dim RNG As Variant
Dim C As Integer

sEmp = EventLog.EmpNum.Value

With Me.EventHistoryLV

    .ListItems.Clear
    .ColumnHeaders.Clear
    .FullRowSelect = True

    .ColumnHeaders.Add , , "Event ID", 20
    .ColumnHeaders.Add , , "EmpNum", 50
    .ColumnHeaders.Add , , "LastName", 80
    .ColumnHeaders.Add , , "FirstName", 80
    .ColumnHeaders.Add , , "Details", 250
    .ColumnHeaders.Add , , "Update Time", 120


    For p = 1 To ws.Cells(Rows.Count, "A").End(xlUp).Row
        If ws.Range("B" & p).Value = sEmp Then
        .ListItems.Add , , ws.Cells(p + 1, "A")
        CLN = CLN + 1
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "B") '1
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "C") '2
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "D") '3
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "E") '4
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "F") '5
        .ListItems(CLN).ListSubItems.Add , , ws.Cells(p + 1, "G") '6
        End If
    Next p
    
End With

End Sub

 

[James006]

Sorry ... but without the file with the UserForm ...cannot help any further

 

[Rex2024]

Sorry ... but without the file with the UserForm ...cannot help any further

I figured out my issue. The p+1 was returning the line I wanted plus 1 line, which was incorrect. I had incorrectly applied the code. Removing the +1 returns the correct info.

Thanks for trying! I really appreciate it

 

[James006]

Glad to hear you managed to fix your problem