Userform question please

[ipbr21054]

Hi,
I have the working code supplied below but because of an issue on my worksheet i now need to use this in a userform.
The userform will just be a command button & should it be two text box i need to use ?

Can you advise please how i would now use the code shown below but from the userform.

Thanks

Private Sub SearchReplaceButton_Click()

  Dim lRow As Long, rng As Range, f As Range
 
  With Worksheets("HONDA LIST")
    lRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set rng = .Range("B4:B" & lRow)
    Set f = rng.Find(.Cells(5, 13).Value, , xlValues, xlWhole)
    If f Is Nothing Then
      MsgBox .Cells(5, 13).Value & " Was Not Found, Please Try Again", vbCritical, "UPDATE VEHICLE INFO MESSAGE"
      Range("M5:N5").ClearContents
    Else
      rng.Replace What:=.Cells(5, 13).Value, Replacement:=.Cells(5, 14).Value, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
      MsgBox "WORKSHEET UPDATED SUCCESSFULLY", vbInformation, "UPDATE VEHICLE INFO MESSAGE"
    End If
    .Cells(4, 1).Select
    Range("M5:N5").ClearContents
  End With
End Sub

 

[GWteB]

So you just want a Userform (with cmd btn) to launch this Sub?
Of course that's possible but it seems meaningless since it requires another Sub to launch your Userform.

 

[ipbr21054]

I will have a button to open the user form.
the userform should have a command button and also 2 text boxes ? Which one will be what to look for & the other to replace it with.
This code works perfect but needs to know work from a form and not the worksheet cells.

 

[Yongle]

Try this (untested)

User code with following objects - TextBox1, TextBox2, CommandButton1

Place code in UserForm module

Private Sub CommandButton1_Click()
    Dim lRow As Long, rng As Range, f As Range

    With Worksheets("HONDA LIST")
        lRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        Set rng = .Range("B4:B" & lRow)
        Set f = rng.Find(TextBox1.Text, , xlValues, xlWhole)
        If f Is Nothing Then
            MsgBox TextBox1.Text & " Was Not Found, Please Try Again", vbCritical, "UPDATE VEHICLE INFO MESSAGE"
        Else
            rng.Replace What:=TextBox1.Text, Replacement:=TextBox2.Text, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
            MsgBox "WORKSHEET UPDATED SUCCESSFULLY", vbInformation, "UPDATE VEHICLE INFO MESSAGE"
        End If
        TextBox1.Value = ""
        TextBox2.Value = ""
    End With
End Sub

 

[ipbr21054]

Worked fine thanks