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

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


Gord Dibben MS Excel MVP

On Sun, 15 Nov 2009 10:59:13 +1300, "Bill Kuunders"
wrote:

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

Thanks Gord

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
See reply at your other post in worksheet.functions group.


Gord Dibben MS Excel MVP

On Sun, 15 Nov 2009 10:59:13 +1300, "Bill Kuunders"
wrote:

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