go to a particular sheet Gord Dibben

[Bill Kuunders]

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

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]

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]

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

 

[Gord Dibben]

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

 

[Bill Kuunders]

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]

Bill

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


Gord

 

[Bill Kuunders]

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]

ws.Activate
Range("A3").select


Gord

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

 

[Bill Kuunders]

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]

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

 

[Bill Kuunders]

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