excel97 vs excel2000 (Find instruction)

[cyrille]

Hi,
I have the following procedure:

1 With Worksheets(1).Range("b10:b5000")
2 Set C = .Find("Science", LookIn:=xlValues, Lookat:=xlWhole)
3 If Not C Is Nothing Then
4 firstAddress = C.Address
5 C.Activate
6 End If
7 End With

It is perfectly working under Excel2000 but not under Excel97. In the
latter case, it stops in line 2 (set C =...) and I obtained the
following error : (1004)
Impossible to read the find property of the range class
(My translation of the error message is probably terrible!).
Can you help me to write this in a different manner ? The funny thing,
this code is given as an example of the Find property in the excel97
help file...
Thanks in advance.
Cyrille

 

[JE McGimpsey]

That code will work in XL97. However, you're probably running it from a
button, right? If so, you need to set the button's Take Focus On Click
property to False.

This was fixed in XL00.

 

[cyrille]

Correct, I was running this function from a button. You're fantastic,
thanks so much !!!

 

[cyrille]

I tought I understood but now I'm not so sure...
I tried something like :

Private Sub CommandButtonA_Click()
If Application.OperatingSystem <> "Windows (32-bit) NT 5.00" Then
MsgBox Application.OperatingSystem
CommandButtonA.TakeFocusOnClick = False
End If
With Worksheets(1).Range("a10:a5000")
Set C = .Find("A", LookIn:=xlValues, Lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
C.Activate
End If
End With
End Sub

Indeed, with Excel97, the message box is open so I imagine that
CommandButtonA.TakeFocusOnClick property is set to false but I continue
to obtain the same error message (error 1004 : Impossible to read the
find property of the range class).
I suppose I did something wrong... Can you help me ??
Thanks in advance
Cyrille

 

[Dave Peterson]

While in design mode (another button on that control toolbox toolbar),
rightclick on the button and show its properties.

The change the .takefocusonclick property to false. (It's a one time manual
effort.)

Alternatively, you could do this:

Private Sub CommandButtonA_Click()
Activecell.activate '<--
With Worksheets(1).Range("a10:a5000")
Set C = .Find("A", LookIn:=xlValues, Lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
C.Activate
End If
End With
End Sub

But now a followup. Is worksheets(1) the same worksheet that holds the button?

If yes, then never mind. But if it's a different worksheet, you'll want to
select/activate that sheet before you activeate that found cell.

Private Sub CommandButtonA_Click()
Activecell.activate '<--
With Worksheets(1).Range("a10:a5000")
Set C = .Find("A", LookIn:=xlValues, Lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
c.parent.select '<--
C.Activate
End If
End With
End Sub