none
How do I set ScrollRow on a Sheet that is not displayed without selecting/activating it? RRS feed

  • Question

  • Without displaying and selecting a named Sheet, how do I set ScrollRow for a Sheet which is not displayed and, therefor, not in a Window?  Is there some other Property of a Sheet I can use to position a specific cell in the upper-left corner of that sheet?

    Thanks for any help.

    Charlie

    Thursday, April 23, 2015 8:39 PM

Answers

  • Without displaying and selecting a named Sheet, how do I set ScrollRow for a Sheet which is not displayed

    You can't and there is no property available.

    The same issue exists for the active cell, it is not possible to set the cells(s) which should be selected when you switch to a sheet before you select the sheet.

    The workaround is to disable the screen and events, switch to the sheet, make your settings and switch back.

    Andreas.

    Sub Test()
      Dim ThisSheet As Object
      
      Set ThisSheet = ActiveSheet
      
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual
      
      Sheets(2).Select
      ActiveWindow.ScrollRow = 123
      ThisSheet.Select
      
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Application.Calculation = xlCalculationAutomatic
    End Sub
    

    • Marked as answer by CharlieWright Friday, April 24, 2015 12:08 PM
    Friday, April 24, 2015 8:44 AM

All replies

  • Hi,

    I do not understand what you mean. Can you maybe explain with an example?

    Regards,

    Reshma


    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    Friday, April 24, 2015 6:13 AM
  • Hi Charlie,

    According to your description, we don’t understand clearly of your requirement.

    Do you mean you have too many sheets and excel application just display some sheets in the bottom, then you want to position to the sheet that not displayed in the bottom?

    If so, you could specify sheet name with range in the name box (upper-left), such as sheet4!D3.

    You also could use this code below to accomplish that:

    Application.Goto Reference:="Sheet6!R7C4"

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 24, 2015 8:17 AM
    Moderator
  • Without displaying and selecting a named Sheet, how do I set ScrollRow for a Sheet which is not displayed

    You can't and there is no property available.

    The same issue exists for the active cell, it is not possible to set the cells(s) which should be selected when you switch to a sheet before you select the sheet.

    The workaround is to disable the screen and events, switch to the sheet, make your settings and switch back.

    Andreas.

    Sub Test()
      Dim ThisSheet As Object
      
      Set ThisSheet = ActiveSheet
      
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual
      
      Sheets(2).Select
      ActiveWindow.ScrollRow = 123
      ThisSheet.Select
      
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Application.Calculation = xlCalculationAutomatic
    End Sub
    

    • Marked as answer by CharlieWright Friday, April 24, 2015 12:08 PM
    Friday, April 24, 2015 8:44 AM
  • Thanks Andreas.  Your answer above confirms my fear/conclusion.  Looks like I'll have to change my macro design to handle events.  Probably the right thing to do anyway.

    To those above who ask me to clarify, here's my situation: I have one macro workbook open and running a macro which requires many minutes to complete.  That macro operates on multiple sheets within its own workbook.  As part of those operations, my intent was to pre-position/scroll various sheets to display potentially different data based on the results of ongoing calculations so that, when the sheet is actually selected, the desired data is already positioned for best visibility.  However, as noted, the macro takes a long time to run.  So I often have multiple workbooks open.  While the macro runs, I minimize the running workbook, and select another workbook that I work in while I wait for the macro to complete. 

    As a result, the running macro workbook is neither displayed nor is it the ActiveWorkbook.  Thus, I cannot allow the macro code to use Properties such as ActiveWindow, since the ActiveWindow is NOT the window I want to adjust.  Nor do I want to take focus away from whatever the ActiveWindow is to make my adjustments to the various sheets in question. 

    As noted above, perhaps I'll need to redesign my workbooks to add event handlers to the various sheets so that my desired adjustments can be made when those sheets are selected, at which point I can be certain that they are the ActiveSheet in the ActiveWindow.

    Thanks all for your help.

    cw

    Friday, April 24, 2015 12:47 PM
  • If you really have such an aversion to scroll there is another way to position the 'future' active cell on a non active sheet, even in a non active workbook, and place it in view similar to Goto

    Set rngActivate = Workbooks("Book2").Worksheets("Sheet3").Range("Z100")
    rngActivate.Copy
    rngActivate.PasteSpecial xlPasteColumnWidths
    Application.CutCopyMode = False
    

    Although not obvious, the PasteSpecial will temporarily activate the sheet, and if necessary the workbook, activate the cell, then return to the original workbook and sheet. As with the Scroll method various activate and deactivate events and a change event will occur so you many need to temporarily disable events. However it shouldn't trigger a calculation so no need to disable that, double check though, if it does you could use xlPasteFormats).

    Sunday, April 26, 2015 6:28 PM
    Moderator
  • Thanks Peter.  I appreciate your post.  It looks like a very clever solution.  I'll check it out.

    cw

    Tuesday, June 2, 2015 7:06 PM