Excel WorkBook Event SheetDeactivate

[tkt_tang]

1. Open an Excel workbook with Sheet1 and Sheet2.

2. Would like Sheet2 to scroll to cell A1 upon the Event of
SheetDeactivate.

3. Background :Sheet1 contains hyperlinks leading to Sheet2 ; when a
hyperlink is clicked, it does not initiate the SheetActivate Event of
Sheet2.

4. Please suggest some means to deploy the Event of SheetDeactivate for
Sheet2 (such that Sheet2 would have already scrolled to cell A1 when it
is opened the next time by the clickable hyperlinks of Sheet1).

5. Regards.

 

[Norman Jones]

Hi TKT,

Since there are only two sheets, why not use the Worksheet_Activate event
for sheet1, e.g.:

'=============>>
Private Sub Worksheet_Activate()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Goto ThisWorkbook.Sheets(2).Range("A1")
Me.Activate
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
'<<=============

 

[tkt_tang]

Mr. Norman Jones,

1. Thank you for your reply to my query.

2. Have made use of several Hyperlink formulae (of Sheet1) to lead to
the corresponding locations on Sheet2.

3. When Sheet2 window is preset to cell A1 at the top left corner, the
hyperlinks would cause the window to display (satisfactorily) at
separate parts of Sheet2.

4. When a hyperlink is clicked, it does not initiate the SheetActivate
Event of Sheet2 ; and therefore, SheetDeactivate would offer a viable
alternative and so that's to say, be prepared to scroll to the top left
corner of Sheet2 beforehand (when it's Deactivated, get ready for the
next cycle).

5. Regards.

 

[Dave Peterson]

I think it depends on the version of excel. xl2003 does fire both the
worksheet_deactivate and _activate.

(I think xl97 doesn't, but xl2k+ does--but I'm not sure of the version.)

I think I'd remove the hyperlinks and use macros so that I'd have more control
over what should happen. Maybe a button--maybe a shape (rectangle over the
cell) that had the code assigned to the shape.

 

[Dave Peterson]

xl2k may suffer the same problem. Maybe it was xl2002+ that fired the events.

 

[tkt_tang]

Mr. Dave Peterson,

Thank you for your response to my query.

In practice, the Activation of one worksheet causes the Deactivation of
another and therefore, the given events are indeed complementary.

The intention is to prepare Sheet2 upon its Deactivation (i.e., moving
the display window to the upper left hand corner of Sheet2 and stay put
thereof ; although at the time, such movememnt is merely taking place
behind the scene since Sheet1 is then activated and therefore, visible
to the user seated before the screen is Sheet1).

When a Hyperlink (of Sheet1) is initiated leading to Sheet2, it does
not fire any event. However, Sheet2 should have been then already well
prepared (meaning that the display window has been preset at the top
left corner of Sheet2) to receive the Hyperlink.

There are more than 2 worksheets in the workbook ; the query is a
simplification for that purpose.

Regards.

 

[Dave Peterson]

What version of excel are you running?

The events fire in xl2002 and xl2003 when you change sheets via a hyperlink.

IIRC, xl2k had a problem that it didn't fire these events when changing sheets
via a hyperlink.

So your choice is to upgrade to a version that supports this or use an
alternative method.

 

[tkt_tang]

Mr. Dave Peterson,

This is Excel 2000 ; How is it possible to take advantage of the event
Workbook_SheetFollowHyperlink in this situation ?

Regards.

 

[Dave Peterson]

I wouldn't do this and I don't own xl2k--so it's untested in that version, but
maybe you could start with something like:

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.Range(Target.SubAddress)
On Error GoTo 0

If myRng Is Nothing Then
Beep
Else
Me.Activate
Application.Goto myRng, scroll:=True
End If
End Sub

I'd remove the hyperlink. Put a rectangle over that cell and assign a macro to
the rectangle that would take me where I wanted.

 

[tkt_tang]

Mr. Dave Peterson,

Thank you for your suggestion.

1. The following Event does not fire (XL 2000) when a Hyperlink is
clicked ; it's observed that the MsgBox does not show.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

MsgBox "Worksheet_FollowHyperlink"

Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.Range(Target.SubAddress)
On Error GoTo 0

If myRng Is Nothing Then
Beep
Else
Me.Activate
Application.Goto myRng, scroll:=True
End If

End Sub

2. =HYPERLINK("#"&ADDRESS(D3+RQS,COLUMN('Volume 1&2'!$D$1),1,TRUE,
CELL("FileName",'Volume 1&2'!$A$1)),
INDIRECT(CHAR(39)&CELL("FileName",'Volume 1&2'!$A$1)&CHAR(39)&CHAR(33)&
ADDRESS(D3,COLUMN('Volume 1&2'!$D$1))))

3. The above Hyperlink will set the display window in proper
perspective if Sheet2 is preset (manually) by scrolling to the top left
corner prior to the Hyperlink activation.

4. The Hyperlinks (of Sheet1) are leading to the sub-headings of Sheet2
tabulation (it's very long and therefore scrolling to a specific
section thereof will be facilated by a hyperlink).

Regards.

 

[Dave Peterson]

It doesn't fire with that kind of hyperlink in xl2003, either.

I'd go back to using the button or shape and a macro.

 

[tkt_tang]

Have you heard ? yes, I have, but barely overheard.

Had a visitor to this office the other day ; this is a rather large
office but without partitions and the fellow talked in the midst of
other guys and gals, lads and lass.

He told them about the basics of rites namely, to prepare an Excel
workbook for attachment to an email and that each worksheet thereof was
to be returned to the top left corner (Ctrl+Home) beforehand.

That's unbearably pedantic, one would reckon, neutrally speaking.

For those who have never heard of Excel Macros would diligently follow
suit by subjecting each worksheet to Ctrl+PageUp and Ctrl+Home.

What do you say ? Is it not entirely politically correct to say that a
worksheet can be returned to the top left corner by taking advantage of
the SheetDeactivate Events respectively ?

Who would need to repeat those menial tabbings of you know what any
longer ?

Please share your experience of Excel Automation. Regards.

 

[Dave Peterson]

I think I'd use a generic macro stored in my personal.xls that went to A1 of
each sheet

option explicit
sub testme099()
dim wks as worksheet
for each wks in activeworkbook.worksheets
application.goto wks.range("a1"), scroll:=true
next wks
end sub

 

[tkt_tang]

Mr. Dave Peterson,

Yes, Thank you ; I'm trying to assimilate the idea.

How and when is it feasible to call the following generic macro stored
in MyPersonal.xls that activates A1 of each sheet ?

option explicit
sub testme099()
dim wks as worksheet
for each wks in activeworkbook.worksheets
application.goto wks.range("a1"), scroll:=true
next wks
end sub

Will study. Regards.

 

[Dave Peterson]

It would be one of those run-on-demand macros. Whenever the user feels like
it. Most likely right before they save (or if they forget, right when they open
it up).