go to a particular sheet Gord Dibben

  • Thread starter Thread starter Bill Kuunders
  • Start date Start date
B

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
Click to expand...


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
 
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
 
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
 
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
 
Bill

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


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
 
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
Click to expand...
 
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
 
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
 
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
 
Back
Top