Excel VBA: Using a counter to count filter results

Question

My sheet named Database has numerous rows of data. The first 2 columns have titles, and the user can select from these titles in a drop down list and filter the data so it only displays results that contain these titles.

In addition to this, I have another subroutine called GetNextResult. The purpose of this is to insert certain values from the filtered results into a text box. This is so the user doesn't have to see rows and rows of results, they can click this button and it'll insert the data into a text box, and every time they click it, it shows another result. When it has shown all of the results, it goes back to showing the first result again. It loops round.

The trouble with this, is the user doesn't know when they've gone back to the first result. I want to add a counter to keep track of what result they're looking at. So the first filtered result will make the counter go to 1, and any subsequent results will be + 1

I have a variable named

First_Row_Filtered

which gives me the value of the C column, on the first row that appears when you filter the data.

Using this variable, I have set up an if statement so that if the textbox contains the same value, the counter will reset to 1, the default position.

The problem I have is that the counter isn't adding 1 to the other results correctly. It has an odd behaviour. When it reaches the value of the first result, it does reset back to 1 which is correct, but the next counter number will remember where it was before it reset and continue counting from there, rather than counting from 1.

As an example: Let's say there are 3 results when you filter the data. When you click the button for the first time, the counter shows 1. This is correct. When you click it the 2nd time it shows 2, correct. When you click it the 3rd time it shows 3, correct. When you click it the 4th time, it shows 1, correct because there is no 4th result, it has looped back to the first result. When you click it the 5th time, it shows 4, it should show 2, but it's continuing the count from before the reset back to 1.

I hope this makes sense, I've put the code below:

Any help would be much appreciated!

Public Sub GetNextResult()



    Call FilterData


    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Database")


    Dim header As String
    header = "txtbox1"

    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row



    Dim DataRange As Range
    Set DataRange = ws.Range("A5", "J" & LastRow)


    Dim FilteredData As Range
    Set FilteredData = DataRange.Resize(ColumnSize:=1).SpecialCells(xlCellTypeVisible)



    First_Row_Filtered = Range("C6:C" & Rows.Count).SpecialCells(xlVisible)(1).Value



    If CurrentRow + 1 > FilteredData.Cells.Count Then
        CurrentRow = 1
    End If




    CurrentRow = CurrentRow + 1




    Dim i As Long
    Dim cell As Variant
    Static counter As Long



    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = counter







    For Each cell In FilteredData


        i = i + 1
        If i = CurrentRow Then
            Call ShowAll


            TextboxName = "txtbox1"
            ActiveSheet.Shapes(TextboxName).DrawingObject.Text = cell.Offset(0, 2)

            TextboxName2 = "txtbox2"
            ActiveSheet.Shapes(TextboxName2).DrawingObject.Text = cell.Offset(0, 3)

            TextboxName3 = "txtbox3"
            ActiveSheet.Shapes(TextboxName3).DrawingObject.Text = cell.Offset(0, 4)


            If ActiveSheet.Shapes(TextboxName).DrawingObject.Text = header Then


                Call GetNextResult
            End If

            Call quick_artwork


        Else
            Call ShowAll


        End If



    Next cell



    If ActiveSheet.Shapes("txtbox1").DrawingObject.Text = First_Row_Filtered Then
    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = 1
    Else
    counter = counter + 1
    End If



End Sub

EDIT: Actually I just realised that when you open the document fresh, the first result shows 1, correct, but the 2nd result shows 0 and then it counts up from there

EDIT2: Updating post with modified code based on responses below:

Public Sub GetNextResult()



    Call FilterData


    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Database")


    Dim header As String
    header = "txtbox1"

    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row



    Dim DataRange As Range
    Set DataRange = ws.Range("A5", "J" & LastRow)


    Dim FilteredData As Range
    Set FilteredData = DataRange.Resize(ColumnSize:=1).SpecialCells(xlCellTypeVisible)



    First_Row_Filtered = Range("C6:C" & Rows.Count).SpecialCells(xlVisible)(1).Value



    If CurrentRow + 1 > FilteredData.Cells.Count Then
        CurrentRow = 1
    End If




    CurrentRow = CurrentRow + 1




    Dim i As Long
    Dim cell As Variant
    Static counter As Long



    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = counter







    For Each cell In FilteredData


        i = i + 1
        If i = CurrentRow Then
            Call ShowAll


            TextboxName = "txtbox1"
            ActiveSheet.Shapes(TextboxName).DrawingObject.Text = cell.Offset(0, 2)

            TextboxName2 = "txtbox2"
            ActiveSheet.Shapes(TextboxName2).DrawingObject.Text = cell.Offset(0, 3)

            TextboxName3 = "txtbox3"
            ActiveSheet.Shapes(TextboxName3).DrawingObject.Text = cell.Offset(0, 4)


            If ActiveSheet.Shapes(TextboxName).DrawingObject.Text = header Then


                Call GetNextResult
            End If

            Call quick_artwork


        Else
            Call ShowAll


        End If



    Next cell



    If ActiveSheet.Shapes("txtbox1").DrawingObject.Text = First_Row_Filtered Then
    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = 1
    counter = 2
    Else
    counter = counter + 1
    End If



End Sub

The last 2 remaining issues are:

1. When the counter should reset, it actually increases by 1 more for half a second and THEN resets to 1. I'm wondering if there's a way to make it happen instantly?

EDIT: This is now resolved, see responses

2. This isn't really related to the counter, but for some reason the GetNextResult button will always insert the 2nd result instead of the 1st when you first click the button. So if you changed the criteria, the first result that would be inserted into the textbox is the 2nd row of the filtered data, not the first. Any idea why?

Answer 1

Your If statement doesn't reset the counter variable. Consider changing your Then statement to simply making counter = 1

    If ActiveSheet.Shapes("txtbox1").DrawingObject.Text = First_Row_Filtered Then counter = 1