I was interested to find out why I can't.
I would like to show cell A3 as the active cell when the sheet is opened.

Thanks

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Bill

Which cell do you want to select when ws.Activate has selected the sheet
with the value in A3?


Gord

On Sun, 15 Nov 2009 14:59:04 +1300, "Bill Kuunders"
wrote:

Thank you Lord. :):)

It works .....!!!!

Testing seems to indicate that the E1:E2000
refers to the number of rows in the original spreadsheet. The one where I
double click somewhere in that range.
I still can't find the right instruction to select a certain cell on the
selected sheet in dummybatch but that doesn't really matter.
At least the right sheet opens up

Thanks heaps Gord
Bill Kuunders
Greetings from Newe Zealand

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Bill

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Const WS_RANGE As String = "E1:E2000"
Dim ws As Worksheet
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False
Workbooks.Open Filename:="C:\Documents and Settings\Bill\My" & _
"Documents\dummybatch.xls"
For Each ws In ActiveWorkbook.Sheets
If ws.Range("A3").Value = Target.Value Then
ws.Activate
End If
Next
End If
endit:
Cancel = True
Application.EnableEvents = True
End Sub

If there is a tie in dummybatch.xls sheets A3 value then last sheet with
the
value is activated.

Do you really have 2000 sheets in dummybatch.xls?

Else why have a range of E1:E2000 to choose a value from?


Gord

On Sun, 15 Nov 2009 10:40:56 +1300, "Bill Kuunders"
wrote:

Eva,
Thanks.
We have a rather large network, and it seems to be too difficult to
find
the workbook via a hyperlink.
I would like to write a macro to open the workbook and find the sheet
where
cell A3 has the same value as the cell I double klicked on the original
sheet.

I have problems shifting the focus from the original to the newly
openend
book.
Something to do with the target in the before double click event?

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"

'ActiveWorkbook.Sheets(Target.Value).Activate
THIS WAS Gord Dibbens solution to find the tab name....
I WOULD LIKE to check each sheet in the dummybatch workbook

Workbooks("dummybatch.xls").Activate
Worksheets(3).Select
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

"Eva" wrote in message
...
Insert/Hyperlink/ and then choose from Look in the spreadsheet and
then
type
the sheet name and cell addres
example
C:\Book1.xls\Sheet1!A1
Click yes if helped


Eva


"Bill Kuunders" wrote:

is there a way to hyperlink from one workbook to a specific worksheet
in
another workbook.


Gord Dibben had this as a solution which works fine.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "A1:A80"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Workbooks.Open Filename:= _
"C:\Program Files\Microsoft Office\Exceldata\Book1.xls"
ActiveWorkbook.Sheets(Target.Value).Activate
End If
End Sub


Gord,
Would there be a way I can look for a value say in cell C2 on each
sheet
rather than the tab name.

Thanks
Bill Kuunders NZ


.