Make a next button in a Userform to jump to next row in a filtered excel sheet

Question

I made a userform with several textboxes, the values in these textboxes are to be filled in or to show values found in an excel sheet.

My problem is to get the Next and OK button to work. When I press Ok, it should dump all the data to the excel sheet on correct row, and with Next button it should move to next row.

The problem is that I can not simply increase a counter because it should jump to next row in the active filter, i.e next row after 45 might be 66. So how can I achieve this?

Below is my code for applying the correct filter:

Dim columnHeader As Integer
Dim ws As Worksheet
Dim Wb As Workbook
Dim lo As ListObject
Dim lsColumn As Integer
Dim counter As Integer
Dim rRange As Range, filRange As Range, Rng As Range

'set the worksheet we will be dealing with
Set Wb = ActiveWorkbook
Set ws = Wb.Sheets("List")
Call Filter.Unhide_All_Columns ' just clear all filters

'Filter our sheet
columnHeader = 2
lsColumn = HelpFunctions.getColumn("LS", ws, columnHeader, True) 'Find correct colum, i.e 18 in this case
Set lo = ws.ListObjects("FilterParts")
lo.Range.AutoFilter Field:=lsColumn, Criteria1:=""
lo.Range.Cells.ClearFormats
lo.AutoFilter.ApplyFilter

'~~> Filter, offset(to exclude headers)
Settings.filRange = lo.Range.Offset(columnHeader, 0).SpecialCells(xlCellTypeVisible).EntireRow ' Assign it to a global variable so it can be reused

I have a function called nextLine(rowNumber as long) which populates the GUI with right values.

How do you know the _current_ row according to which you can determine the _next_ one? Do you start showing the data of the first row of the filter results and then you aspect to go forward to the next rows with the _next_ button? — EttoreP, May 30 '16 at 17:05
If you are merely looking for the next visible row on a filtered sheet (which is what I understand) then this post a probably a duplicate of the following and should be closed as such: http://stackoverflow.com/questions/21593675/find-the-next-visible-row — Ralph, May 30 '16 at 17:31
@Ralph , Thanks for the tip, I used the entirerow.hidden tip in the link you provided. — skatun, Jun 01 '16 at 07:42

score 1 · Answer 1 · answered Jun 01 '16 at 14:31

Your code looks good except for one thing in my opinion, in the init method when you do:

Settings.nextIndex = columnHeader + 1
ws.Cells(Settings.nextIndex, 1).Select

Call Creo.updateGUI(Settings.nextIndex)

you will show in the GUI the third line of the table instead of the first line filtered (that i expect it's what you want) because columnheader is been set to 2 and then you do the code above that will gave to you nextindex = 3 (columnheader + 1).
Instead the code above you can do

Settings.NextIndex = columnHeader
call nextButton()

and let the nextButton sub to find the first row filtered and show it in the GUI. I've never tested the code, it's just an idea reading yours.

Ettore

score 0 · Answer 2 · answered Jun 01 '16 at 07:46

So here is my next button command, I do not completly like the active cell method though(might fail if someone click in the sheet while using the GUI.

'*********************************************************************************************************
'****************** This one gets triggered when the next button event occurs ****************************
'*********************************************************************************************************
Public Sub nextButton()
    Dim i As Long
    For i = Settings.nextIndex To Settings.filRange.Row
        ActiveCell.Offset(1, 0).Select
        If Not ActiveCell.EntireRow.Hidden Then
            Exit For
        End If
    Next i
    Settings.nextIndex = ActiveCell.Row
    Call Creo.updateGUI(Settings.nextIndex)


    'Reached last row
    If Settings.nextIndex = Settings.filRange.Row Then
        'Remove any filters
        Call Filter.Unhide_All_Columns
        'Hide GUI
        MOM.Hide
    End If

End Sub

Here is my init method:

'*******************************************************************************************************
'****************** THIS ONE GETS CALLED FROM EXCEL ****************************************************
'*******************************************************************************************************

Sub addNewLs()
    Dim columnHeader As Integer
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lo As ListObject
    Dim lsColumn As Integer
    Dim counter As Integer
    Dim rRange As Range, filRange As Range, Rng As Range

    'set the worksheet we will be dealing with
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("List")
    Call Filter.Unhide_All_Columns ' just clear all filters

    'Filter our sheet
    columnHeader = 2
    lsColumn = HelpFunctions.getColumn("LS", ws, columnHeader, True) 'Find correct colum, i.e 18 in this case
    Set lo = ws.ListObjects("FilterParts")
    lo.Range.AutoFilter Field:=lsColumn, Criteria1:=""
    lo.Range.Cells.ClearFormats
    lo.AutoFilter.ApplyFilter

    Set Settings.filRange = lo.Range.Cells(lo.Range.Rows.Count, lo.Range.Columns.Count)

    'lo.Range.Offset(columnHeader, 0).SpecialCells(xlCellTypeVisible).EntireRow ' Assign it to a global variable so it can be reused

    Settings.nextIndex = columnHeader + 1
    ws.Cells(Settings.nextIndex, 1).Select

    Call Creo.updateGUI(Settings.nextIndex)
    Call MOMModule.initiliazeMOM
End Sub

Make a next button in a Userform to jump to next row in a filtered excel sheet

2 Answers2