Using same code for Multiple cmd Controls
 

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

Using same code for Multiple cmd Controls
 

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


"Ruan" wrote in message
...
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

Using same code for Multiple cmd Controls
 

Thank you Rob

That works out perfectly.

Ruan


"Rob van Gelder" wrote in message
...
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


"Ruan" wrote in message
...
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