What links a click/tap position to select an object on screen?

Scroll row

• QUESTION: in Excel 2007, I have a workbook containing two sheets - a calculation sheet and a HELP sheet. When the user presses the HELP button on the calc sheet, it activates the HELP sheet and uses the ActiveSheet.ScrollRow property to scroll to the relevant topic, depending on which section of the calc sheet the user was filling in at the time. This works quickly and well. When the HELP sheet is activated by a click of its nametag, instead of the calc sheet HELP button, I want it to be scrolled to the top of the sheet, so I wrote the code as a WorkSheet_Activate macro in the HELP sheet's code window. It worked, but not perfectly: as you left-click the sheet tab the HELP instructions are still in their previously-scrolled position and then scroll to the top as you release the mouse button. So I thought I would scroll the HELP sheet to the top as I exited it, by writing the code as a WorkSheet_Deactivate routine. However, this code does not run until another sheet (the calc sheet) is activated and by then ActiveSheet refers to the calc sheet, not the HELP sheet. I know I can load data into cells on non-active sheets but can I scroll a non-active sheet? Is there another way to link ScrollRow=1, or similar command, to a deactivated sheet? I am struggling to understand how to use the VBE to see if such a link is possible. Thank you for your time. Terry ANSWER: Terry, this worked for me Private Sub Worksheet_Deactivate() On Error GoTo ErrHandler Debug.Print "Deactivate " & Me.Name Application.ScreenUpdating = False Application.EnableEvents = False Set Sh = ActiveSheet Me.Activate ActiveWindow.ScrollRow = 1 Me.Range("A1").Select Sh.Activate ErrHandler: Application.ScreenUpdating = True Application.EnableEvents = True End Sub ---------- FOLLOW-UP ---------- QUESTION: Thanks as always for your timely solution to my scrolling question. Despite the flicker that always results as screen-updating is switched off then on, your code worked better than anything I could come up with. Because it contained several statements I had seen before but never used, I placed a break in the code and stepped through it to try and find out exactly what it did. I am afraid that the one original question has now multiplied to five! Am I correct in assuming that "Me" is a short-cut way of saying this object (sheet)? Why the provision of an error-handler? What is the purpose of the Debug.Print statement, which obviously can do more than display values in the immediate window? What events have been disabled, or is that just precautionary? Is it necessary, or better, to use the Set statement in this case, rather than just reactivating a named sheet? I hope you can satisfy my curiosity on these matters. Terry

• Answer:

  Terry, Debug.print just prints text to the immediate window. I was using it for debugging and forgot to take it out in a class module, such as a worksheet module, me refers to the object that owns the class. So in a worksheet module it is a predefined reference to the worksheet that owns the module. In a userform module, to the userform. I the Thisworkbook module, to the workbook. Disabling events disables allevents. Since I am activating the worksheet in the deactivate event and then deactivating it, I don't want to cause a recursive call to the event resulting in a stack overflow error. >Is it necessary, or better, to use the Set statement in this case, rather than just reactivating a named sheet? Well, I don't know how many sheets you have in your workbook. If you have more than two, how would you know which sheet to return to if you. I you used a specific sheet name, then you wouldn't necessarily return the user to the sheet where they went to when they triggered the deactivate event. at least that was my thinking when I used set sh = Activesheet . . . sh.Activate I use error handling, so if an error does occur in the event after I have disabled events, I want to make sure they get reenabled. Hope that answers your questions.