VBA .Find question

[Guest]

Can anybody tell me why I get the error "Unable to get the Find property of
the Range Class" here? I passed the variable a to MyTest5(b). I assumed
that MyTest5 would Find a in the range "Name". Help is appreciated!

Sub MyTest()

Dim a As String
Dim c

a = Worksheets("Input Sheet").Range("C3").Value
c = Application.Match(a, Worksheets("Sheet2").Range("Name"), 0)

If Not IsError(c) Then
Dim mymessage As String

mymessage = "This name has already been entered." & Chr(13) _
& "Would you like to retrieve that listing?"
Style = vbYesNo
Response = MsgBox(mymessage, Style)

If Response = vbYes Then
Call MyTest5(a)
Else: GoTo Option3
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub MyTest5(b As String)

With Worksheets(2).Range("Name")
Set d = .Find(b, , xlValues, xlWhole)
If Not d Is Nothing Then

 

[JE McGimpsey]

VBA is not finding a range named "Name" in Worksheets(2).

Note that Worksheets(2) will be different than Sheets("Sheet2") if the
sheet named "Sheet2" is not the second sheet from left to right in the
worksheet tabs.

 

[Guest]

"Sheet2" and Sheet(2) are the same thing. There's only two sheets in the
workbook. And Column A is named "Name"

 

[JE McGimpsey]

In that case, it works for me. Here's my test workbook:

ftp://ftp.mcgimpsey.com/excel/mark1_demo.xls

 

[Guest]

OK,
Here's something I discovered. When I run the macro from inside the VBE
using the F8 key, it works. However, when I run it using the command button

 

[JE McGimpsey]

Set the Button's TakeFocusOnClick property to False.

 

[Guest]

Sweet mercy, it worked!!!!!!
Thanks a million!

Set the Button's TakeFocusOnClick property to False.