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 problem is that 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?
Appreciate the help!
Note: As per the rules, I've also posted this on StackOverflow but haven't any outcome from there. I've posted a similar question to this here and received some great support so figured I would ask this question too.
https://stackoverflow.com/questions/57506181/excel-vba-filtered-data-returns-2nd-result-when-calling-each-row
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.
Code:
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 SubThe problem is that 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?
Appreciate the help!
Note: As per the rules, I've also posted this on StackOverflow but haven't any outcome from there. I've posted a similar question to this here and received some great support so figured I would ask this question too.
https://stackoverflow.com/questions/57506181/excel-vba-filtered-data-returns-2nd-result-when-calling-each-row
Last edited:
