Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use the following to reset a worksheet view to it's uppermost view position.

    Code:
    v1.Activate		 'workbook
    w1.Select
    w1.Activate		 'worksheet
    With w1
    	Range("A1:R1").Select
    	Selection.AutoFilter
    	Cells.Select
    	Cells.EntireColumn.AutoFit
    	Range("A1:R1").Select
    	Selection.Interior.ColorIndex = 34
    	Selection.AutoFilter
    	Range("A1").Select
    	Range("A2").Select
    	ActiveWindow.SmallScroll Up:=1
    	Range("A1").Select
    End With
    ...... where row(1)'s pane is frozen. I find that this works in some cases but not in others....?? Is there a more reliable approach, or a reason why this does not always work?

    The exact same code is in different modules, works in some but not in others. When I step through the code, each line is executed but the sheet does not reset to top.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Nathan

    Name cell A1 top

    then try this:

    [codebox]Sub Reset()
    ActiveWindow.Panes(1).Activate
    Range("top").Select
    ActiveWindow.Panes(2).Activate
    Range("top").Select

    End Sub[/codebox]
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Other one is a little longer.....

    It requires A1 to be named top and A2 to be named next

    [codebox]Sub Reset2()
    Dim i As Integer

    For i = 1 To 2
    ActiveWindow.Panes(i).Activate
    Range("top").Select
    Next i

    ActiveWindow.SmallScroll Down:=1
    Range("next").Select

    End Sub[/codebox]
    Jerry

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Jerry,

    I cannot get your code to execute, it fails for me at:

    ActiveWindow.Panes(1).Activate

    P.s. I have updated my original post.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='765442' date='14-Mar-2009 22:51']...

    P.s. I have updated my original post.[/quote]

    Yes, you did update your post whilst I was writing a response and then you changed the text so it all becomes meaningless.

    I took it that you had a pane freeze at the top of the sheet [ActiveWindow.Panes(1).Activate] which made cell A1 selected then it dropped to the next pane and did the same, but as you have made your question more complicated with the change it made it meaningless.....never mind another 20 minutes of my life wasted.
    Jerry

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry if my edit confused matters. I do have the top row frozen!

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is weird!!, but I have found the problem.

    Application.ScreenUpdating = False

    ........

    Application.ScreenUpdating = True

    Yet I have other almost identical modules that also turn off screenupdating and they work perfectly normal. Could the module be corrupt? or is there a module specific hidden setting that could be wrong?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you are loooking at the sheet when the screenupdating is disabled, it is not surprising that the view is not yet "reset" since the code has told excel not to update the view. The view will not be reset until the screenupdating is enabled...

    Steve

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='765464' date='15-Mar-2009 10:05']If you are loooking at the sheet when the screenupdating is disabled, it is not surprising that the view is not yet "reset" since the code has told excel not to update the view. The view will not be reset until the screenupdating is enabled...

    Steve[/quote]
    That is the problem Steve, of course I would not expect the screen to update when the setting is false, but when I switch back to true, the screen still does not reset, even though all the other changes made by the code have been successful.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    [quote name='VegasNath' post='765467' date='15-Mar-2009 10:48']That is the problem Steve, of course I would not expect the screen to update when the setting is false, but when I switch back to true, the screen still does not reset, even though all the other changes made by the code have been successful.[/quote]

    Selecting cells in code does not change the view and is almost always unnecessary and inefficient. The only thing you have which will change the view is this:
    Code:
    ActiveWindow.SmallScroll Up:=1
    but that is literally a small scroll up from wherever the view happens to be. If you need to have A1 visible, you could just use:
    Code:
    application.Goto Range("A1"), true
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •