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











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks