 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2006Excel WorkBook Event SheetDeactivate
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. 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 '<<============= --- Regards, Norman > 1. Open an Excel workbook with Sheet1 and Sheet2. > [quoted text clipped - 10 lines] > > 5. Regards. 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. 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. > 1. Open an Excel workbook with Sheet1 and Sheet2. > [quoted text clipped - 10 lines] > > 5. Regards.  SignatureDave Peterson xl2k may suffer the same problem. Maybe it was xl2002+ that fired the events. > I think it depends on the version of excel. xl2003 does fire both the > worksheet_deactivate and _activate. [quoted text clipped - 23 lines] > > Dave Peterson SignatureDave Peterson 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. 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. > Mr. Dave Peterson, > [quoted text clipped - 18 lines] > > Regards. SignatureDave Peterson Mr. Dave Peterson, This is Excel 2000 ; How is it possible to take advantage of the event Workbook_SheetFollowHyperlink in this situation ? Regards. 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. > Mr. Dave Peterson, > > This is Excel 2000 ; How is it possible to take advantage of the event > Workbook_SheetFollowHyperlink in this situation ? > > Regards. SignatureDave Peterson 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. 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. > Mr. Dave Peterson, > [quoted text clipped - 37 lines] > > Regards. SignatureDave Peterson 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. 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 > Have you heard ? yes, I have, but barely overheard. > [quoted text clipped - 19 lines] > > Please share your experience of Excel Automation. Regards. SignatureDave Peterson 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. 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). > Mr. Dave Peterson, > [quoted text clipped - 12 lines] > > Will study. Regards. SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.