Get value from filtered table vba with button

ybr_15

Board Regular
Joined
May 24, 2016
Messages
204
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top