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

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


.

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


.

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


.

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


.

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


.

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


.

ws.Activate
Range("A3").select


Gord


On Mon, 16 Nov 2009 09:37:02 +1300, "Bill Kuunders"
wrote:

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


.

It doesn't want to do it.
Obviously I can get the same result if I introduce a "before close"
instruction.
Just interested to see why Range("A3").select
does not work.

Thanks Gord


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
ws.Activate
Range("A3").select


Gord


On Mon, 16 Nov 2009 09:37:02 +1300, "Bill Kuunders"
wrote:

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
m...
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


.

Because I did not qualify the sheet reference. Apologies for not testing.

Either of these are OK

ws.Range("A3").select

or

If ws.Range("A3").Value = Target.Value Then
With ws
.Activate
.Range("A3").Select
End With
End If


Gord

On Mon, 16 Nov 2009 10:13:57 +1300, "Bill Kuunders"
wrote:

It doesn't want to do it.
Obviously I can get the same result if I introduce a "before close"
instruction.
Just interested to see why Range("A3").select
does not work.

Thanks Gord


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
ws.Activate
Range("A3").select


Gord


On Mon, 16 Nov 2009 09:37:02 +1300, "Bill Kuunders"
wrote:

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
om...
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


.

Well, That was soooooo obvious ....NOT

Who would have thought. Normally we don't have to do that.
Again Thanks a lot Gord

Greetings from New Zealand
Bill Kuunders

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Because I did not qualify the sheet reference. Apologies for not testing.

Either of these are OK

ws.Range("A3").select

or

If ws.Range("A3").Value = Target.Value Then
With ws
.Activate
.Range("A3").Select
End With
End If


Gord

On Mon, 16 Nov 2009 10:13:57 +1300, "Bill Kuunders"
wrote:

It doesn't want to do it.
Obviously I can get the same result if I introduce a "before close"
instruction.
Just interested to see why Range("A3").select
does not work.

Thanks Gord


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
ws.Activate
Range("A3").select


Gord


On Mon, 16 Nov 2009 09:37:02 +1300, "Bill Kuunders"
wrote:

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
m...
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
news:5ncuf51a84r0h83lh4s1dcobgv629fr0ju@4ax. com...
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


.