Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ensuring Selected Cell is Visible on Screen
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ensuring Selected Cell is Visible on Screen
Sub Macro1()
TargetLine = 99 Application.Goto Reference:=Cells(TargetLine, 5) End Sub -- Gary''s Student - gsnu200775 "ZipCurs" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ensuring Selected Cell is Visible on Screen
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. "Gary''s Student" wrote: Sub Macro1() TargetLine = 99 Application.Goto Reference:=Cells(TargetLine, 5) End Sub -- Gary''s Student - gsnu200775 "ZipCurs" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ensuring Selected Cell is Visible on Screen
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" wrote: Sub Macro1() TargetLine = 99 Application.Goto Reference:=Cells(TargetLine, 5) End Sub -- Gary''s Student - gsnu200775 "ZipCurs" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ensuring Selected Cell is Visible on Screen
Your solution looks pretty good.
-- Gary''s Student - gsnu200775 "ZipCurs" wrote: 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" wrote: Sub Macro1() TargetLine = 99 Application.Goto Reference:=Cells(TargetLine, 5) End Sub -- Gary''s Student - gsnu200775 "ZipCurs" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ensuring Selected Cell is Visible on Screen
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" wrote in message ... 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" wrote: Sub Macro1() TargetLine = 99 Application.Goto Reference:=Cells(TargetLine, 5) End Sub -- Gary''s Student - gsnu200775 "ZipCurs" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ensuring Selected Cell is Visible on Screen
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" wrote: 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" wrote in message ... 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" wrote: Sub Macro1() TargetLine = 99 Application.Goto Reference:=Cells(TargetLine, 5) End Sub -- Gary''s Student - gsnu200775 "ZipCurs" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ensuring Selected Cell is Visible on Screen
There was a typo in the example I posted -
Application.Goto ActiveSheet.Cells(r.Row, _ ActiveWindow.VisibleRange(1).Column), True 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 "ZipCurs" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: Sub Macro1() TargetLine = 99 Application.Goto Reference:=Cells(TargetLine, 5) End Sub -- Gary''s Student - gsnu200775 "ZipCurs" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ensuring Autofilter arrows are visible at end of macro | Excel Programming | |||
scroll window to display selected cell in visible area? | Excel Programming | |||
Make selected row visible to user when out of screen view | Excel Programming | |||
Macro to Move Selected Cell to Top of Screen | Excel Programming | |||
Macro to Move Selected Cell to Top of Screen | Excel Programming |