jimalya,
In your userform you have a textbox that display what record the user is currently viewing ('1 of 3', '2 of 3', etc). You also have Previous and Next buttons which either are not yet coded or you did not provide their codes. I have assumed that the textbox displaying what record the user is currently viewing (the 1, 2, etc) is named txtActiveRecord, so you'll need to adjust the code to the correct name for that textbox.
With that adjustment, this should work for you (this is the CommandButton1_Click code):
Private Sub CommandButton1_Click()
Dim CheckCell As Range
Dim FilteredCell As Range
Dim rngFiltered As Range
Dim ActiveRecord As Range
Dim i As Long
'Filter the data
With ActiveSheet.Range("A1:X200")
.AutoFilter 'Remove any active filters
.AutoFilter Field:=1, Criteria1:="*" & ComboBox1.Value
.AutoFilter Field:=2, Criteria1:="*" & ComboBox2.Value
.AutoFilter Field:=3, Criteria1:="*" & ComboBox3.Value
.AutoFilter Field:=4, Criteria1:="*" & ComboBox4.Value
.AutoFilter Field:=5, Criteria1:="*" & ComboBox5.Value
.AutoFilter Field:=6, Criteria1:="*" & ComboBox6.Value
.AutoFilter Field:=7, Criteria1:="*" & ComboBox7.Value
End With
'Check for data
With Intersect(ActiveSheet.AutoFilter.Range, Range("A:A"))
On Error Resume Next
Set rngFiltered = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rngFiltered Is Nothing Then
'No data, display message and exit sub
MsgBox "There is no data with the selected filters", , "No Data"
Exit Sub
End If
'There is data, show the number of visible rows (number of filtered records)
frmFilter.txtOptionEnd.Value = rngFiltered.Cells.Count
'Get the active record
'This is based on the number that is in a textbox named txtActiveRecord which hold the number for the selected record
'The textbox's number is adjusted by the Previous/Next buttons
'If empty, the textbox's number should be set to 1
If Len(txtActiveRecord.Text) = 0 Then txtActiveRecord.Text = 1
For Each FilteredCell In rngFiltered.Cells
i = i + 1
If i = CLng(txtActiveRecord.Text) Then
Set ActiveRecord = FilteredCell
Exit For
End If
Next FilteredCell
'Get non-blank entries for the ActiveRecord and populate the appropriate textboxes
i = 0
For Each CheckCell In Intersect(ActiveRecord.EntireRow, Range("H:O"))
If Len(CheckCell.Text) > 0 Then
i = i + 1
Controls("txtWC" & i).Text = Cells(1, CheckCell.Column).Text
Controls("txtNotes" & i).Text = CheckCell.Text
End If
Next CheckCell
End Sub
Bookmarks