Ensuring Selected Cell is Visible on Screen

[ZipCurs]

I am running an macro on a worksheet that is has a horizontally split screen
that is frozen. In addition, I have set the ScrollArea to limit the range of
scrolling on the lower pane. At the end of my macro, I select a cell that is
in a row that I would like to be visible on the screen.

If I use something like:

ActiveWindow.LargeScroll Up:=10000
TargetR = TargetLine
ActiveWindow.ScrollRow = TargetR

It works great at bringing the desired cell to the top of the lower pane,
unless the limits of the scrollarea conflict, in which case this does not
work.

I tried:

Cells(TargetLine, 5).Select
CellBottom = ActiveCell.Top
ActiveWindow.Panes(1).ScrollIntoView Left:=0, Top:=CellBottom, Width:=100,
Height:=75, Start:=False

This kind of works by itself, but doesn't work everytime. When I embed this
in my macro it doesn't work at all.

I am not at all committed to having the cell at the top or bottom. I just
want it to be visible, work with Excel 2007, and for different computers that
might have different display dimensions.

Hopefully this is clear. Thank you in advance for any help.

 

[Gary''s Student]

Sub Macro1()

TargetLine = 99
Application.Goto Reference:=Cells(TargetLine, 5)
End Sub

 

[ZipCurs]

Thank you for getting back so quickly.

I was hoping that your very simple solution would do the job. I added a
Scroll:= True to get it to move to the desired row, and it worked fine...
except for the problem cases at the bottom of the scroll area. In these
cases, it simply does nothing. Basically, Excel does no seem to want to move
the bottom of the scroll area to the top of the window. I suppose I could
add a buffer to the scroll area, but this seems like a tough thing to do
since it will vary depending on the display and zoom.

 

[ZipCurs]

I have hobbled together the following solution, which seems to work. If you
have any better, more elegant solutions, please let me know.

InWindow = False
LastPick = 1
Do
'Check to see if desired cell is visible yet
If TargetLine <= ActiveWindow.VisibleRange.Row Then
'If it is indicate as such
InWindow = True
Else
'If not, look to see if scroll even did anything
If LastPick = ActiveWindow.VisibleRange.Row Then
'If not, scrolling is done and cell should be visible
InWindow = True
Else
'If scroll did have an effect scroll again
LastPick = ActiveWindow.VisibleRange.Row
ActiveWindow.SmallScroll down:=1
End If
End If

Loop Until InWindow = True

 

[Gary''s Student]

Your solution looks pretty good.

 

[Peter T]

If Intersect(activecell.Rows(1).EntireRow, _
ActiveWindow.VisibleRange) Is Nothing Then

Application.Goto ActiveSheet.Cells(r.Row, _
ActiveWindow.VisibleRange(1).Column), True

End If

The above doesn't necessarily bring the ActiveCell into the view, only its
row, but appears to be equivalent to what your code does. That is of course
if I follow the objective. However if you want view the ActiveCell do -
Application.Goto ActiveCell, True

Note, if there's any possibility of an ActiveChart on the sheet would need
to use Windows(2) and also qualify ActiveCell with Windows(2)

Regards,
Peter T

 

[ZipCurs]

Thanks for the suggestion. I think I have my solution for now. I do wonder
about the GoTo command though, it seems like this will have the same problem
with conflicting with the Scroll Area as I was having earlier.

 

[Peter T]

There was a typo in the example I posted -

Application.Goto ActiveSheet.Cells(ActiveCell.Row, _
ActiveWindow.VisibleRange(1).Column), True

IOW, change the "r" to ActiveCell

re -

I do wonder about the
GoTo command though, it seems like this will have the same problem
with conflicting with the Scroll Area as I was having earlier.

Did you try setting the 2nd argument for Goto True, as in my example

Regards,
Peter T