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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Morning,
I have a userform which has a command button on it with the code as shown below,

Rich (BB code):
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Maybe just have the userform open & the values already loaded into listbox without the need to press command button on userform
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top