CenterOnCell Range(Target.Address(External:=True)

Although, the centeroncell may be fighting the hyperlink - you might have to
adjust Chips code.


--
Regards,
Tom Ogilvy



"Jay Fincannon" wrote in message
...
Hello.
After following a hyperlink from a 'home' sheet to a specific cell in
another sheet, I would like to call Chip Pearson's CenterOnCell(onCell
as Range) procedure but I can't find the right combination to pass the
(onCell) parameter.

here's what I've tried.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

CenterOnCell Target
and
CenterOnCell Target.Address
and
CenterOnCell Target.Range

End Sub

Thank you too much already.

Tom
I get Wrong number of arguments or invalid property assignment (Error
450) Error breaks at .Address, the Hyperlink address is "", I tried
using the SubAddress but that didn't work either.
CenterOnCell works ok if called from other than a Hyperlink.
on inspection I know the cell i need to goto. even tried hard coding
Call CenterOnCell ($B$415) that didn't work either.
Jay

On Tue, 26 Aug 2003 12:02:43 -0400, "Tom Ogilvy"
wrote:

CenterOnCell Range(Target.Address(External:=True)

Although, the centeroncell may be fighting the hyperlink - you might have to
adjust Chips code.

It should have been subaddress without the external:=true - went braindead
for a while.

but that didn't work in my test - gave me another type of error - as
suggested, I think it is fighting the event. Anyway, this worked:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.OnTime Now, "Dummy"
End Sub


now create, in a general module, a sub named dummy

Sub Dummy()
CenterOnCell ActiveCell
End Sub


An alternative would be to use the beforedoubleclick event or even selection
change for a cell and skip the hyperlink.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$E$5" Then
Cancel = True
CenterOnCell Worksheets("Sheet3").Range("Z35")
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$E$6" Then
CenterOnCell Worksheets("Sheet3").Range("Z35")
End If
End Sub

use one or the other.
You could format the cell to look like a hyperlink if you are using
selection change or for beforedoubleclick, put in text like

doubleclick to go to Z35




--
Regards,
Tom Ogilvy


"Jay Fincannon" wrote in message
...
Tom
I get Wrong number of arguments or invalid property assignment (Error
450) Error breaks at .Address, the Hyperlink address is "", I tried
using the SubAddress but that didn't work either.
CenterOnCell works ok if called from other than a Hyperlink.
on inspection I know the cell i need to goto. even tried hard coding
Call CenterOnCell ($B$415) that didn't work either.
Jay

On Tue, 26 Aug 2003 12:02:43 -0400, "Tom Ogilvy"
wrote:

CenterOnCell Range(Target.Address(External:=True)

Although, the centeroncell may be fighting the hyperlink - you might have
to
adjust Chips code.

Tom
I like the "Dummy" one best, thanks
Another problem.
I have a workbook with 111 worksheets representing route books.
Column A has street names then column B contains the customer address
numbers for that street.
After using the VBA Find method to locate an address number how can
you back up to get the street name. Here's a piece of my code.
everything is Dim'd properly.

Sub FindRoute_Num(Num As String)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < "LookMeUp" Then

Set uRange = ws.UsedRange
Set FindNum = uRange.Find(Num, , , Look)

If Not FindNum Is Nothing Then
FirstHit = FindNum.Address
' I would like to backup here and get the street name
and add it to hlText array
Do
hlTarget = FindNum.Address
hlText(0) = ws.Name: hlText(1) = FindNum
Sheets("LookMeUp").Hyperlinks.Add _
Anchor:=Cells(r, 1), _
Address:="", SubAddress:=ws.Name & "!" & hlTarget, _
TextToDisplay:=Join(hlText, " ")
r = r + 1
Found = Found + 1
Set FindNum = uRange.FindNext(FindNum)
Loop While Not FindNum Is Nothing And FindNum.Address < _
FirstHit
End If
End If
Next



On Tue, 26 Aug 2003 13:23:45 -0400, "Tom Ogilvy"
wrote:

It should have been subaddress without the external:=true - went braindead
for a while.

but that didn't work in my test - gave me another type of error - as
suggested, I think it is fighting the event. Anyway, this worked:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.OnTime Now, "Dummy"
End Sub


now create, in a general module, a sub named dummy

Sub Dummy()
CenterOnCell ActiveCell
End Sub


An alternative would be to use the beforedoubleclick event or even selection
change for a cell and skip the hyperlink.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$E$5" Then
Cancel = True
CenterOnCell Worksheets("Sheet3").Range("Z35")
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$E$6" Then
CenterOnCell Worksheets("Sheet3").Range("Z35")
End If
End Sub

use one or the other.
You could format the cell to look like a hyperlink if you are using
selection change or for beforedoubleclick, put in text like

doubleclick to go to Z35

hlText(0) = ws.Name: hlText(1) = FindNum

FindNum is a range object, so hlText(1) will use the default property which
is value - so htText(1) will hold the value of the found cell (the address
you were looking for). I think you want
set hlText(1) = FindNum

Regardless, a range object isn't just a cell address - it is the cell and
the cell knows where it is located.


Address:="", SubAddress:=FindNum.Address(external:=True)

to illustrate from the immediate window:

set findnum = Activecell
? findnum.Address(external:=True)
[Book1]Sheet1!$E$28

I have found that anytime i need a string showing something like Sheet1!A1,
that using the external option with the address property gets the job done.

But to answer you question about the street name - if it is in column A

sStreetName = findnum.offset(0,-1).Value



--
Regards,
Tom Ogilvy



"Jay Fincannon" wrote in message
...
Tom
I like the "Dummy" one best, thanks
Another problem.
I have a workbook with 111 worksheets representing route books.
Column A has street names then column B contains the customer address
numbers for that street.
After using the VBA Find method to locate an address number how can
you back up to get the street name. Here's a piece of my code.
everything is Dim'd properly.

Sub FindRoute_Num(Num As String)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < "LookMeUp" Then

Set uRange = ws.UsedRange
Set FindNum = uRange.Find(Num, , , Look)

If Not FindNum Is Nothing Then
FirstHit = FindNum.Address
' I would like to backup here and get the street name
and add it to hlText array
Do
hlTarget = FindNum.Address
hlText(0) = ws.Name: hlText(1) = FindNum
Sheets("LookMeUp").Hyperlinks.Add _
Anchor:=Cells(r, 1), _
Address:="", SubAddress:=ws.Name & "!" & hlTarget, _
TextToDisplay:=Join(hlText, " ")
r = r + 1
Found = Found + 1
Set FindNum = uRange.FindNext(FindNum)
Loop While Not FindNum Is Nothing And FindNum.Address < _
FirstHit
End If
End If
Next



On Tue, 26 Aug 2003 13:23:45 -0400, "Tom Ogilvy"
wrote:

It should have been subaddress without the external:=true - went
braindead
for a while.

but that didn't work in my test - gave me another type of error - as
suggested, I think it is fighting the event. Anyway, this worked:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.OnTime Now, "Dummy"
End Sub


now create, in a general module, a sub named dummy

Sub Dummy()
CenterOnCell ActiveCell
End Sub


An alternative would be to use the beforedoubleclick event or even
selection
change for a cell and skip the hyperlink.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$E$5" Then
Cancel = True
CenterOnCell Worksheets("Sheet3").Range("Z35")
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$E$6" Then
CenterOnCell Worksheets("Sheet3").Range("Z35")
End If
End Sub

use one or the other.
You could format the cell to look like a hyperlink if you are using
selection change or for beforedoubleclick, put in text like

doubleclick to go to Z35

Thanx again Tom.
problem with offset(0.-1) is street usually more than 1 row up.
A B
..
..
414 N. Main St
415 3732
416 3738
417 3744
etc...
How to get from B417 to A414

On Wed, 27 Aug 2003 08:53:22 -0400, "Tom Ogilvy"
wrote:

hlText(0) = ws.Name: hlText(1) = FindNum

FindNum is a range object, so hlText(1) will use the default property which
is value - so htText(1) will hold the value of the found cell (the address
you were looking for). I think you want
set hlText(1) = FindNum

Regardless, a range object isn't just a cell address - it is the cell and
the cell knows where it is located.


Address:="", SubAddress:=FindNum.Address(external:=True)

to illustrate from the immediate window:

set findnum = Activecell
? findnum.Address(external:=True)
[Book1]Sheet1!$E$28

I have found that anytime i need a string showing something like Sheet1!A1,
that using the external option with the address property gets the job done.

But to answer you question about the street name - if it is in column A

sStreetName = findnum.offset(0,-1).Value