trying to get my userform to advance through visible records and not all records

[kbishop94]

My workbook contains a worksheet that has entries that are "incidents" in each row. (these are quality related incidents for my company)

Column A contains the Incident ID. This is how each individual incident is identified and sourced.

Right now the userform I have for editing a record allows the user to advance forward or backward to the next/previous record. It does this (going to the next record) regardless of how the worksheet might be filtered at the time which results with some rows being hidden at the time. I need to make it so that it goes to the next record that is visible on worksheet at that time. (if that is possibe?)

Its easier to explain using a few pictures to show how it functions: (the worksheet page where the records are kept is on the right, a snippet for the VBA on how it locates a specific record is on the left at the top, and part of the userform is on the left at the bottom)


After the incident ID number is found (column A on the worksheet), it populates that form with the data from that row.

There are 4 "navigation" buttons on the userform too.

Two of navigation buttons (the small yellow ones on either side of .txtIncidentID1 button) allow the user to advance forward or backward to the next/previous record WITHOUT saving.

The two other larger buttons (with the red "UPDATE" on them) advance/go back but SAVES the record before doing so:




The advancing to the next/previous record is where I need to change it so that it advances but going in the order of whatever happens to be showing (visible) on the worksheet at the time when the record is selected.

here is the code for how it locates the record that is entered into the userform into the textbox ".tctIncidentID1"

Private Sub txtIncidentID1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim FoundCell As Range
    Dim Search As String
' Lookup Incident ID Number
    Search = Me.txtIncidentID1.value
    Set FoundCell = Sheet1.Range("A:A").Find(Search, LookAt:=xlWhole, LookIn:=xlValues)
    If Not FoundCell Is Nothing Then
        GetRecord FoundCell.Row
    Else
        MsgBox Search & Chr(10) & "Incident ID not found.", 48, "Not Found"
        Me.txtIncidentID1.value = ""
        Cancel = True
    End If
Me.cmdAdd1.Enabled = Not Cancel
End Sub

Next it runs this sub (only showing part of it as its very lengthy) for finding the specific record in order to populate the userform with the data contained in the row for that incident:

Sub GetRecord(ByVal RecordRow As Long)
Dim ckALL As String
Dim ckFault As String
Dim ckWhos As String
Dim ckCAPA As String
Dim ws As Worksheet
Set ws = Sheet1
    With Me
        .txtIncidentID1.value = ws.Cells(RecordRow, 1).Text
        .txtDateBox1 = ws.Cells(RecordRow, 2)               ' Date Incident Occurred:
        .cboPriority1 = ws.Cells(RecordRow, 4)              ' Incident GRADE (1 - 5) (code is shown below)
        .cboLocation1 = ws.Cells(RecordRow, 6)              ' Facility where the Incident occurred:
        .cboCustomer1 = ws.Cells(RecordRow, 7)              ' Incident involved WHAT customer: (if applicable)
        .cboEndcustomer1 = ws.Cells(RecordRow, 8)           ' Incident involved WHAT customer: (SECONDARY cust; if applicable)
        .txtProblem1 = ws.Cells(RecordRow, 9)               ' Description of the incident:
        .txtAction1 = ws.Cells(RecordRow, 10)               ' Desccription of actions taken to address the incident:
        .cboIssuedBy1 = ws.Cells(RecordRow, 11)             ' Incident was reported by:
        .cboOnBehalfOf1 = ws.Cells(RecordRow, 12)           ' Incident was entered by:
        .cboIssuedTo1 = ws.Cells(RecordRow, 13)             ' dept incident was most related to:
        .cboIssuedTo21 = ws.Cells(RecordRow, 14)            ' dept incident was most related to:  (SECONDARY dept)
        .txtSummary = ws.Cells(RecordRow, 15)               ' incident "summary" data including effectiveness of CAR (if a CAR was issued to this incident):
        .txtEnteredOn = ws.Cells(RecordRow, 16)             ' this is the original date/time that the incident was entered (this cannot be edited):
        .txtCost1 = ws.Cells(RecordRow, 17)                 ' total cost of the incident:
        
        .txtIncidentCodes = ws.Cells(RecordRow, 18)         ' incident codes for all the checkboxes for incident reasons:

And here is an example of the code for advancing to the next record (in this case going to the next one up... plus one)

Private Sub cmdNext_Click()
Dim Ctl
    For Each Ctl In Me.Controls
        If TypeOf Ctl Is MSForms.TextBox Then
            Ctl.Text = ""
        ElseIf TypeOf Ctl Is MSForms.ComboBox Then
            Ctl.Object.value = ""
        ElseIf TypeOf Ctl Is MSForms.CheckBox Then
            Ctl.Object.value = False
        End If
    Next Ctl
    GetRecord Val(cmdNext.Tag + 1)
End Sub

How can I make it so that it advances/goes back to the next record that is shown on the screen at that time? Use the SpecialCells (xlCellTypeVisible)? How would that work?

Thanks in advance for any suggestions or help to do this.

 

[Akuini]

Try something like this (I'm using my own example):

Dim c As Range
Private Sub CommandButton1_Click()
tx = "X-001" 'id to find
    Set c = Sheets("Sheet1").Range("A:A").Find(What:=tx, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
End Sub

Private Sub CommandButton2_Click()
'after finding the ID then get next visible row
If Not c Is Nothing Then
    Do
        Set c = c.Offset(1)
    Loop While c.EntireRow.Hidden = True
Debug.Print c.Row
End If

End Sub

 

[kbishop94]

Thank you, Akuini. Your code works perfectly for what I was trying to achieve.

I changed it slightly so that the userform populates with data from the record (the next visible record) that your code locates.

Private Sub cmdNext_Click()
Dim Ctl
' FIRST CLEAR ALL CHECKBOXES & TEXTBOXES THAT CONTAINED DATA FROM PREVIOUS INCIDENT ID RECORD.
    For Each Ctl In Me.Controls
        If TypeOf Ctl Is MSForms.TextBox Then
            Ctl.Text = ""
        ElseIf TypeOf Ctl Is MSForms.ComboBox Then
            Ctl.Object.value = ""
        ElseIf TypeOf Ctl Is MSForms.CheckBox Then
            Ctl.Object.value = False
        End If
    Next Ctl
' NOW FIND THE NEXT VISIBLE ROW **BELOW** THE CURRENT INCIDENT ID RECORD & POPULATE THE USERFORM WITH THAT DATA
        If Not FoundCell Is Nothing Then
            Do
                Set FoundCell = FoundCell.Offset(1)
                Loop While FoundCell.EntireRow.Hidden = True
        Debug.Print FoundCell.Row
        GetRecord FoundCell.Row
    End If
End Sub

 

[Akuini]

You're welcome & glad it works.
I think you can remove this line:
Debug.Print FoundCell.Row