Set focus ?

  • Thread starter Thread starter purplehaz
  • Start date Start date
P

purplehaz

I have a spreadsheet that has column names in columns A-F, then
data(numbers) in the cells under each column. A totals(sum) section for all
the numbers is at row AA500 - AF500. I also am using split and locked panes
so when you scroll the data portion the column names are always there. The
prob is that the totals are on AA row 500 in excel and its a pain to scroll
to row 500 all the time. Is there a way to write vb code(macro) to set the
focus of the worksheet to certain cells? I want to be able to run a macro
and have it scroll down and over to the totals section and kind of center it
on the screen. I tried ActiveWindow.SmallScroll.... command and it seemed to
work, but not quite how I wanted it. Is there a better way? Thanks.
 
Is it possible to have your totals to one of the top rows within the top
split area or locked panes.
such as: AA(1) is the title
AA2 is the sum(aa3:aa500)
split screen starts arter aa2
 
Hi

ActiveWindow.ScrollRow = 500
will put row 500 on top of your moving part of the screen. Center is impossible, dependant
as it is on window size, window zoom, screen resolution and surrounding column widths and
row heights.

All this said, I think I'd go for hyperlinks instead of scrolling macros. Links are far
more intuitive for "navigate to"-stuff.
 
I have a spreadsheet that has column names in columns A-F, then
data(numbers) in the cells under each column. A totals(sum) section for
all
the numbers is at row AA500 - AF500. I also am using split and locked
panes
so when you scroll the data portion the column names are always there.
The
prob is that the totals are on AA row 500 in excel and its a pain to
scroll
to row 500 all the time. Is there a way to write vb code(macro) to set
the
focus of the worksheet to certain cells?
Click to expand...

Here are a few suggestions. From a post in ms.pub.excel.misc,
activesheet.scrollarea="$a$1:$iv$5"
to remove
=""
Click to expand...

Another suggestion, lifted from the same thread: replicate your totals
from AA500-AF5000 in the first few rows of A-F. Now you can freeze the
panes so that the totals and the column headers are always visible.

One other option is to use the goto function. Name the region AA500-AF500
something instructive, like "Totals." Now you can hit F5 and select
Totals from the available list. to jump to the desired location. Hit the
F5 key again and the cell from which you jumped should be an available
choice, which allows you to go back to the original cell.

Finally, if the AA500-AF500 is the bottom right corner of your
spreadsheet, the control-end key should get you there. I don't know how
to go back to the original cell using this keystroke, so that is a
definite limitation of this approach.
 
Thanks for the info. I thought center would be impossible but thought I'd
throw it out. The scrollrow certainly helps me out. Do you have an example
of how I can use hyperlinks to diplay the totals area? Thanks.
 
Thanks for all the suggestions. These are great. I should be able to do what
I want now, thanks alot.

I have a spreadsheet that has column names in columns A-F, then
data(numbers) in the cells under each column. A totals(sum) section for
all
the numbers is at row AA500 - AF500. I also am using split and locked
panes
so when you scroll the data portion the column names are always there.
The
prob is that the totals are on AA row 500 in excel and its a pain to
scroll
to row 500 all the time. Is there a way to write vb code(macro) to set
the
focus of the worksheet to certain cells?
Click to expand...

Here are a few suggestions. From a post in ms.pub.excel.misc,
activesheet.scrollarea="$a$1:$iv$5"
to remove
=""
Click to expand...

Another suggestion, lifted from the same thread: replicate your totals
from AA500-AF5000 in the first few rows of A-F. Now you can freeze the
panes so that the totals and the column headers are always visible.

One other option is to use the goto function. Name the region AA500-AF500
something instructive, like "Totals." Now you can hit F5 and select
Totals from the available list. to jump to the desired location. Hit the
F5 key again and the cell from which you jumped should be an available
choice, which allows you to go back to the original cell.

Finally, if the AA500-AF500 is the bottom right corner of your
spreadsheet, the control-end key should get you there. I don't know how
to go back to the original cell using this keystroke, so that is a
definite limitation of this approach.
 
Back
Top