Setting up Previous and next Buttons for search results

[Bill Williamson]

Hello everyone,
I have my userform almost done. I am able to do my searches, a it sorts the data properly. It will show first match in my form, but have added Prev and Next Buttons to look through similar matches. I am not sure how to get it to work with the search results.
Here is a screen shot of my sorted Data.https://www.dropbox.com/s/l18ksc0750y4xr7/Screenshot 2019-10-24 12.13.17.png?dl=0

Here is a link to my file,
https://www.dropbox.com/s/jvz52b3gjvo2r9x/BOM Review Setup3.xlsm?dl=0


Any ideas are appreciated.

My Code,

Dim wsData As Worksheet
Dim Fnd As Range
Const xlUpdate As Integer = 2


Private Sub CancelButton_Click()
Unload Me
End Sub


Private Sub CBNext_Click()


End Sub


Private Sub CBPrev_Click()


End Sub


Private Sub ClearButton_Click()
'Sub Clearform()
Dim ctrl As msforms.Control
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
        Case "TextBox"
            ctrl.Text = ""
        Case "ComboBox"
            ctrl.ListIndex = -1
        Case "CheckBox"
            ctrl.Value = False
    End Select
Next
End Sub


Private Sub CMDSearch_Click()
    Dim i As Integer
    With wsData
        If .AutoFilterMode Then .AutoFilterMode = False
        If Customer.Value <> "" Then .Range("A1").AutoFilter 1, Me.Customer.Value
        If CSONumber.Value <> "" Then .Range("A1").AutoFilter 2, Me.CSONumber.Value
        If JobNumber.Value <> "" Then .Range("A1").AutoFilter 3, Me.JobNumber.Value
        On Error Resume Next
        Set Fnd = .Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1)
        On Error GoTo 0
        
        If Fnd Is Nothing Then
            MsgBox "Search term not found", 48, "Not Found"
            Me.CMDUpdate.Enabled = False
        Else
            
            For i = 1 To 15
                With Me.Controls(Choose(i, "Customer", "CSONumber", "JobNumber", _
                                            "PCWeldType", "PCWeldGrind", "PCFinish", _
                                            "NonPCWeld", "NonPCGrind", "NonPCFinish", _
                                            "BRReview", "BOMReview", "DimReview", _
                                            "WeldReview", "Apperance", "Complete"))
                    If i < 10 Then
                        .Text = Fnd.Offset(, i - 1).Value
                    Else
                        .Value = CBool(LCase(Fnd.Offset(, i - 1).Value) = "yes")
                    End If
                End With
            Next i
                
            Me.CMDUpdate.Enabled = True
            End If
'Turns off auto filter, shows all data
 '           .AutoFilterMode = False
            
        End With
End Sub




Private Sub CMDUpdate_Click()
   AddUpdateRecord Fnd.Row, xlUpdate
End Sub


Sub AddUpdateRecord(ByVal RecordRow As Long, ByVal Action As Integer)
    Dim i As Integer
    Dim Answer As VbMsgBoxResult
    
    If Action = xlUpdate Then
'Update Records
        Answer = MsgBox("Are you sure you want to update?", vbYesNo + vbQuestion, "Update Record")
        If Answer = vbNo Then Exit Sub
    End If
    
    With wsData
        For i = 1 To 9
            .Cells(RecordRow, i).Value = Choose(i, Customer.Value, CSONumber.Value, JobNumber.Value, _
                                                   PCWeldType.Value, PCWeldGrind.Value, PCFinish.Value, _
                                                   NonPCWeld.Value, NonPCGrind.Value, NonPCFinish.Value)
        Next i
    
            .Cells(RecordRow, 10).Value = IIf(BRReview.Value, "Yes", "No")
            .Cells(RecordRow, 11).Value = IIf(BOMReview.Value, "Yes", "No")
            .Cells(RecordRow, 12).Value = IIf(DimReview.Value, "Yes", "No")
            .Cells(RecordRow, 13).Value = IIf(WeldReview.Value, "Yes", "No")
            .Cells(RecordRow, 14).Value = IIf(Apperance.Value, "Yes", "No")
            .Cells(RecordRow, 15).Value = IIf(Complete.Value, "Yes", "No")
    End With
    msg = IIf(xlUpdate, "Updated", "Added")
    MsgBox "Record " & msg & " To Worksheet", 64, "Record " & msg




'Sub Clearform()
  Dim ctrl As msforms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next








End Sub




Private Sub Complete_Click()
Dim oCtrl As Control
    For Each oControl In Me.Controls
If TypeOf oControl Is msforms.CheckBox Then
oControl.Value = Complete.Value
End If
Next
End Sub


Private Sub OKButton_Click()
Dim EmptyRow As Long
'Make Sheet1 Active
    Sheet1.Activate


'Determine Empty Row
EmptyRow = WorksheetFunction.CountA(Range("A:A"))




'Transfer Information
Cells(EmptyRow, 1).Value = Customer.Value
Cells(EmptyRow, 2).Value = CSONumber.Value
Cells(EmptyRow, 3).Value = JobNumber.Value
Cells(EmptyRow, 4).Value = PCWeldType.Value
Cells(EmptyRow, 5).Value = PCWeldGrind.Value
Cells(EmptyRow, 6).Value = PCFinish.Value
Cells(EmptyRow, 7).Value = NonPCWeld.Value
Cells(EmptyRow, 8).Value = NonPCGrind.Value
Cells(EmptyRow, 9).Value = NonPCFinish.Value


If BRReview.Value = True Then Cells(EmptyRow, 10).Value = "Yes"
If BRReview.Value = False Then Cells(EmptyRow, 10).Value = "No"


If BOMReview.Value = True Then Cells(EmptyRow, 11).Value = "Yes"
If BOMReview.Value = False Then Cells(EmptyRow, 11).Value = "No"


If DimReview.Value = True Then Cells(EmptyRow, 12).Value = "Yes"
If DimReview.Value = False Then Cells(EmptyRow, 12).Value = "No"


If WeldReview.Value = True Then Cells(EmptyRow, 13).Value = "Yes"
If WeldReview.Value = False Then Cells(EmptyRow, 13).Value = "No"
  
If Apperance.Value = True Then Cells(EmptyRow, 14).Value = "Yes"
If Apperance.Value = False Then Cells(EmptyRow, 14).Value = "No"
 
If Complete.Value = True Then Cells(EmptyRow, 15).Value = "Yes"
If Complete.Value = False Then Cells(EmptyRow, 15).Value = "No"
MsgBox "Record Added"




'Sub Clearform()
  Dim ctrl As msforms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next


End Sub






Private Sub ClearForm()
  Dim ctrl As msforms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next
  End Sub
  
  
  Set Fnd = Nothing
  Me.CMDUpdate.Enabled = False
End Sub




Private Sub UserForm_Initialize()
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    Me.CMDUpdate.Enabled = False
End Sub

Thanks,

Bill Williamson

 

[DanteAmor]

Hi Bill, Try this

Remove this event:

CMDSearch_Click()

And add this events:

Private Sub [COLOR=#0000ff]CBNext_Click[/COLOR]()
  With wsData
    For i = Fnd.Row + 1 To .Range("A" & Rows.Count).End(xlUp).Row
      If .Range("A" & i).EntireRow.Hidden = False Then
        If .Range("A" & i).Value <> "" Then
          Set Fnd = Range("A" & i)
        Else
          MsgBox "Last row"
        End If
        Exit For
      End If
    Next
  End With
  Call FillControls
End Sub
'
Private Sub [COLOR=#0000ff]CBPrev_Click[/COLOR]()
  With wsData
    For i = Fnd.Row - 1 To 1 Step -1
      If i = 1 Then
        MsgBox "Fisrt row"
        Exit For
      End If
      If .Range("A" & i).EntireRow.Hidden = False Then
        If .Range("A" & i).Value <> "" Then
          Set Fnd = Range("A" & i)
        End If
        Exit For
      End If
    Next
  End With
  Call FillControls
End Sub
'
Private Sub [COLOR=#0000ff]CMDSearch_Click[/COLOR]()
  Application.ScreenUpdating = False
  Dim i As Integer
  With wsData
      If .AutoFilterMode Then .AutoFilterMode = False
      If Customer.Value <> "" Then .Range("A1").AutoFilter 1, Me.Customer.Value
      If CSONumber.Value <> "" Then .Range("A1").AutoFilter 2, Me.CSONumber.Value
      If JobNumber.Value <> "" Then .Range("A1").AutoFilter 3, Me.JobNumber.Value
      On Error Resume Next
      Set Fnd = .Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1)
      On Error GoTo 0
      '
      If Fnd Is Nothing Then
          MsgBox "Search term not found", 48, "Not Found"
          Me.CMDUpdate.Enabled = False
      Else
          Call FillControls
          Me.CMDUpdate.Enabled = True
      End If
  End With
  Application.ScreenUpdating = True
End Sub
'
Sub [COLOR=#0000ff]FillControls[/COLOR]()
  For i = 1 To 15
    With Me.Controls(Choose(i, "Customer", "CSONumber", "JobNumber", "PCWeldType", "PCWeldGrind", _
                               "PCFinish", "NonPCWeld", "NonPCGrind", "NonPCFinish", "BRReview", _
                               "BOMReview", "DimReview", "WeldReview", "Apperance", "Complete"))
      If i < 10 Then
          .Text = Fnd.Offset(, i - 1).Value
      Else
          .Value = CBool(LCase(Fnd.Offset(, i - 1).Value) = "yes")
      End If
    End With
  Next i
End Sub

 

[Bill Williamson]

Dante Amor,

That Worked Great,
Thanks.

My Message box for "not found" in my search does not function (Has not for the past few revisions),
Do you by chance see where I missed somthing.


Best Regards,

Bill Williamson

 

[DanteAmor]

Try this

Private Sub CMDSearch_Click()
  Application.ScreenUpdating = False
  Dim i As Integer, n As Variant
  With wsData
      If .AutoFilterMode Then .AutoFilterMode = False
      If Customer.Value <> "" Then .Range("A1").AutoFilter 1, Me.Customer.Value
      If CSONumber.Value <> "" Then .Range("A1").AutoFilter 2, Me.CSONumber.Value
      If JobNumber.Value <> "" Then .Range("A1").AutoFilter 3, Me.JobNumber.Value
      On Error Resume Next
      Set Fnd = .Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1)
[COLOR=#0000ff]      n = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Count[/COLOR]
      On Error GoTo 0
      If n < 2 Then
          MsgBox "Search term not found", 48, "Not Found"
          Me.CMDUpdate.Enabled = False
      Else
          Call FillControls
          Me.CMDUpdate.Enabled = True
      End If
  End With
  Application.ScreenUpdating = True
End Sub

 

[Bill Williamson]

Hello Dante Amor,

Thank you for your help with this project. I entered the code,
getting a Compile Error:

"End With Without With"

It Errors on the End With Line.



Regards,

Bill Williamson

 

[DanteAmor]

Did you copy the code completely?
Did you modify the code?

 

[Bill Williamson]

Hmmm..
Ok, I recopied code, and put it in again, No longer getting Compile error, Must have missed somthing when typing.

But when it finds no results I still dont get Message Box "No results"

thanks,


Bill,

 

[Bill Williamson]

I have a Theory of why it may not be working, but dont know enough to know if I am on the right track or not.

My Idea is that I am not getting the error msg because im never making the condition for it.
Im thinking fnd is always all of the data, but the autofilter is just hiding it.....


does that make any sense at all?


Bill

 

[DanteAmor]

Try with test file

https://www.dropbox.com/s/vozxs73lztbq47f/BOM Review Setup3.xlsm?dl=0

 

[Bill Williamson]

DanteAmor

That worked great...... Thank you,
But i am not sure why, the code for the search looks the same?
Can you explain at all what was causing the problem?

Thanks Bill