double click event search in other workbook

[Bill Kuunders]

I would like to be able to double click onto a cell and use the sheet double
click event macro to
open a different workbook and search in that book for the same value in a
particular cell (a3) in each sheet.
If there is a match then activate that sheet.

Thanks
Bill Kuunders NZ

My code
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Const WS_RANGE As String = "E1:E2000"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Workbooks.Open Filename:="C:\Documents and Settings\Bill\My
Documents\dummybatch.xls"

I WOULD LIKE to check each sheet in the dummybatch workbook

Workbooks("dummybatch.xls").Activate
Worksheets(3).Select

THIS WORKS but
Range("A3").Activate
I JUST CAN'T get the above step to work
The Range A3 value does not change from the original workbook to the newly
opened book / sheet /a3 value
and for the stuff below ....I'm guessing a bit

For Each Sheet In Sheets
Range("A3").Select
If Target.Value = Range("A3").Value Then Sheet.Activate

Next

End If
End Sub

 

[Gord Dibben]

See reply at your other post in worksheet.functions group.


Gord Dibben MS Excel MVP

 

[Bill Kuunders]

Thanks Gord

See reply at your other post in worksheet.functions group.


Gord Dibben MS Excel MVP