Excel limit records userform displays to those filtered 2

[MrMode]

I have two userforms.

The first (frmRecords) shows each row as a record with navigation buttons (First, Next, Previous, Last). It also has a text box giving the number of the record (i.e. 1) and label that gives the total number of records (i.e. of 7). This works correctly and allows the user to cycle through all the records in the worksheet, edit and add new ones.

The second (frmSearch) allows filters to be applied to the worksheet - this is working correctly.

When the second closes, the first is reopened, however, it shows all the records, not the filtered recordset!

How do I get frmRecords to only show the filtered records in the worksheet?

I used the Office Dev example to help me construct the userform

https://msdn.microsoft.com/en-us/library/office/aa192538(v=office.11).aspx

I am assuming I need to add some qualification in here as it is called when the userform is initialized.

Private Sub GetData()
'error handling

'GetData copies the data from the currently active worksheet to the user form.
'After declaring a temporary variable r to hold the current row,
'the routine verifies that the value in the RowNumber control is numeric.

Dim r As Long
LastRow = FindLastRow

If IsNumeric(RowNumber.Text) Then
    r = CLng(RowNumber.Text)
    
'Knowing that RowNumber contains a numeric value, the CLng function is used to convert
'the value in RowNumber into the variable r. The rest of the code merely uses r to
'extract the information from the proper row and copy it to the correct field.

Else
    ClearData
'ClearData routine simply assigns an empty string to each field on the form to clear out
'any values that might have already been displayed on the form.
    
    MsgBox "Illegal row number"
    Exit Sub
    
End If

'Populate all the controls on the userform

    Index.Text = Cells(r, 1)
    Category1.Text = Cells(r, 2)
    Contractref.Text = Cells(r, 3)
    Title.Text = Cells(r, 4)
    Description.Text = Cells(r, 5)
    Contracttype1.Text = Cells(r, 6)
    Status1.Text = Cells(r, 7)
    Targetdate = Cells(r, 8)
    Tenderlist = Cells(r, 9)
    Subcontract = Cells(r, 10)
    Value1 = Cells(r, 11)

    DisableSave

'error handling

End Sub

 

[combo]

I think that before starting coding the problem, you should rethink how it will work and what you need. You started to interact with worksheet, it's OK, but you need to be careful. IMHO (the "filtered form"):
1) FindLastRow variable, found with .End(xlDown).Row, applied to filtered range, returns last visible row. I would keep it as a project level variable, in standard module, and calculate it for non-filtered data. It can be modified when you add/remove records. If you still need a filter, use your original method,
2) you need additional variable (Long) for equivalent to "cursor" - an indicator for row currently displayed. Tis is your "r", I would use it rather as a base for RowNumber text, not opposite,
3) having range Range(Cells(2,1),Cells(LastRow,1) and current record r displayed, it's not a problem to find next/previous/first/last valid (visible) row, I would use new temporary variable for this, If I found it, assign it to r and display contents.
Mind that in [tt]For Each c In Range(Cells(2, 1), Cells(FindLastRow, 1))[/tt] c is a range, so next you should use it in the way: [tt]If Not c.EntireRow.Hidden Then[/tt]. I don't understand what you plan after [tt]'test for visibility and loop to the end of the recordset...[/tt]. If you plan to keep visibility of rows in MyArray, then declare it as Boolean, loop with [tt]For i=1 ToLastRow[/tt] and set [tt]MyArray(i)=Not Cells(i, 1).EntireRow.Hidden[/tt]. However, this can be tested directly. That are my impressions, you need to build a logic of the problem and code it.

combo

 

[MrMode]

I have solved the problem in a different way.

I want the userform to only show the records that meet certain criteria. Rather than trying to pull the data from the worksheet, I have gone down the ADO route and created a recordset which means I don't have to think about the worksheet anymore, just work with the recordset until I am ready to amend or add something to the original records.

    Dim cn As ADODB.connection
    Dim rs As ADODB.recordset
    Dim strFile As String
    Dim strCon As String
    Dim strsql As String
    
    strFile = Workbooks(1).FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    
    'Create a new connection and recordset
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
       
    'store the SQL statement
    strsql = "SELECT * FROM [Sheet1$]" & FilterOptions
        
    'Map connection and recordset to module level variables
    Set mADOCon = cn
    Set mADORs = rs
    mADORs.CursorLocation = adUseClient
    
    'Open the connection and recordset
    mADOCon.Open strCon
    mADORs.Open strsql, mADOCon, adOpenDynamic
    
    'Go to the first record
    mADORs.MoveFirst
    
    'Add relative position and total record number to label
    Me.Label1 = "Record " & mADORs.AbsolutePosition & " of " & mADORs.RecordCount

This allows me to use MoveFirst, MovePrevious, MoveNext, MoveLast for record navigation through the recordset (not worrying about the underlying records in the worksheet and the associated code needed to figure out position in relation to the other unhidden records). This has eliminated the issue with blank records being displayed in the userform.

Thank you for all your help.