Get value from filtered table vba with button

ybr_15

Board Regular
Joined
May 24, 2016
Messages
164
I create a simple previous-next button using spinbutton to get data from the table, here the code:
Code:
Private Sub spnPrevNext_SpinDown()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim shtHelper As Worksheet, shtData As Worksheet
    Dim lobTable As ListObject
    Dim FindData as Range
    Dim strCriteria As String
    
    Set shtHelper = wb.Sheets("Helper")
    Set shtData = wb.Sheets("Data")
    Set lobTable = shtData.ListObjects("Table1")
    
    Application.ScreenUpdating = False
    strCriteria = shtData.Range("B12").Value
    On Error Resume Next
    Set FindData = lobTable.ListColumns(1).DataBodyRange.Find(What:=strCriteria, LookIn:=xlValues, LookAt:=xlWhole).Offset(-1, 0)
    If FindData.Value = lobTable.ListColumns(1).DataBodyRange.Cells(1).Offset(-1, 0).Value Then Exit Sub
    shtData.Range("B12").Value = FindData.Value
    Application.ScreenUpdating = True
End Sub

Private Sub spnPrevNext_SpinUp()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim shtHelper As Worksheet, shtData As Worksheet
    Dim lobTable As ListObject
    Dim FindData as Range
    Dim strCriteria As String
    
    Set shtHelper = wb.Sheets("Helper")
    Set shtData = wb.Sheets("Data")
    Set lobTable = shtData.ListObjects("Table1")
    
    Application.ScreenUpdating = False
    strCriteria = shtData.Range("B12").Value
    On Error Resume Next
    Set FindData = lobTable.ListColumns(1).DataBodyRange.Find(What:=strCriteria, LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0)
    If FindData.Value = "" Then Exit Sub
    shtData.Range("B12").Value = FindData.Value
    Application.ScreenUpdating = True
End Sub
The code above work properly. But when the table is filtered, the button cannot get the value (Previous or Next) from visible data in filtered table. Can you help me for solution, please? So the button can run in the filtered table or not. Thanks
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,089,866
Messages
5,410,877
Members
403,332
Latest member
ffoott

This Week's Hot Topics

Top