How to use userform command button code on a worksheet command button

[ipbr21054]

Morning,
I have a userform which has a command button on it with the code as shown below,

Private Sub SniffButton_Click()
  TextBoxCUSTOMER.Value = ""
  TextBoxMAKE.Value = ""
  TextBoxMODEL.Value = ""
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("DETAILS")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "0;180;150;150;100;80;60"
    Set r = Range("G3", Range("G" & Rows.Count).End(xlUp))
    Set f = r.Find("YES", LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
          .List(i, 1) = f.Offset(, -6).Value
          .List(i, 2) = f.Offset(, -5).Value
          .List(i, 3) = f.Offset(, -4).Value
          .List(i, 4) = f.Offset(, -3).Value
          .List(i, 5) = f.Offset(, 0).Value
          .List(i, 6) = f.Offset(, 1).Value
            added = True
            Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -6).Value
          .List(.ListCount - 1, 2) = f.Offset(, -5).Value
          .List(.ListCount - 1, 3) = f.Offset(, -4).Value
          .List(.ListCount - 1, 4) = f.Offset(, -3).Value
          .List(.ListCount - 1, 5) = f.Offset(, 0).Value
          .List(.ListCount - 1, 6) = f.Offset(, 1).Value
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      .TopIndex = 0
      Else
      MsgBox "NO SNIFF DATA WAS FOUND", vbCritical, "CLONING INFORMATION MESSAGE"
    End If
  End With
End Sub

This is what i would like to do.
Have a command button on my worksheet & use the code above.
Sometimes there is no need for me to open the userform & then press the command button hence why i would like to put the code on its own command button on my worksheet.

Can you please advise.
Just placing the code above on a command button on the worksheet obvioulsy gives me an error message,
Run time error 424 Object required.

 

[ipbr21054]

Maybe just have the userform open & the values already loaded into listbox without the need to press command button on userform

 

[ipbr21054]

Ive now done it.
I copied the same userform & removed all the unwanted buttons etc.
I then put the code onto the userform initialise & now it opens the form & runs the code automatically