double click event search in other workbook
 

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

double click event search in other workbook
 

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

double click event search in other workbook
 

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