Using same code for Multiple cmd Controls

  • Thread starter Thread starter Ruan
  • Start date Start date
R

Ruan

Hello,

I have 12 identical Worksheets, each with a Text Box and Command Button
control for Finding clients in Column 2. I would like each Command Button to
activate the same code.

In the "Module" code window, I have the following -

Public Sub Find_Name(sht As Worksheet)
Dim sRange As Range, myRange As Range
Dim searchString As String

searchString = txtFind.Value

With sht
Set sRange = .UsedRange.Columns(2)
Set myRange = sRange.Find(searchString, LookIn:=xlValues,
MatchCase:=False)

' If found select matching cell
If Not myRange Is Nothing Then
myRange.Select
MsgBox "Found Match"
Else
MsgBox "Name Not Found"
End If

End With
End Sub


In each Sheet code window I have the following, with the number representing
the Month.

' Worksheet for June
Private Sub cmdFind6_Click()

Find_Name ActiveSheet, txtFind6.Value

End Sub


I am not sure why this does not work. Please help.
Ruan
 
Ruan,

It appears that Find_Name only takes one parameter: sht
You need to make it accept two parameters:
eg.
Public Sub Find_Name(sht Worksheet, FindString as String)

Then where it says:
searchString = txtFind.Value

you need to change that line to:
searchstring = FindString

Let me know how that goes.

Rob
 
Thank you Rob

That works out perfectly.

Ruan


Rob van Gelder said:
Ruan,

It appears that Find_Name only takes one parameter: sht
You need to make it accept two parameters:
eg.
Public Sub Find_Name(sht Worksheet, FindString as String)

Then where it says:
searchString = txtFind.Value

you need to change that line to:
searchstring = FindString

Let me know how that goes.

Rob


Button
Click to expand...
 
Back
Top