I need those functions...

I need a way to get the address range of the cells that are currently
showing on the screen for a given worksheet. OR a way to know if a cell or
range of cells are currently "on the screen". Screen resolution and the size
of each cell will have to be taken into account.

I have thought about using API calls to get the screen resolution and then
using .height and .width to get the X,Y,H,W values of every cell's
(rectangle) then from that, calculate which cells are currently showing on
the screen.

Is there a better way?

I am trying to come up with some routines that can be used keep pertinent
data showing on the screen no matter how the user has their screen sizing
set. (with in certain boundaries of course, I.E. at least 15 rows and 4
columns or some preset limit; perhaps an upper limit too) I could also see
using this to know when to scroll the screen to keep a block of cells near
the center of the screen.

--
Regards,
John

maybe you could use:

MsgBox ActiveWindow.VisibleRange.Address
or
MsgBox ActiveWindow.VisibleRange.Cells.SpecialCells(xlCel lTypeVisible).Address

But as a user, I wouldn't like what you're trying to do. If I've arranged
multiple windows so I could see stuff. I wouldn't want something to change my
layout.

John Keith wrote:

I need those functions...

I need a way to get the address range of the cells that are currently
showing on the screen for a given worksheet. OR a way to know if a cell or
range of cells are currently "on the screen". Screen resolution and the size
of each cell will have to be taken into account.

I have thought about using API calls to get the screen resolution and then
using .height and .width to get the X,Y,H,W values of every cell's
(rectangle) then from that, calculate which cells are currently showing on
the screen.

Is there a better way?

I am trying to come up with some routines that can be used keep pertinent
data showing on the screen no matter how the user has their screen sizing
set. (with in certain boundaries of course, I.E. at least 15 rows and 4
columns or some preset limit; perhaps an upper limit too) I could also see
using this to know when to scroll the screen to keep a block of cells near
the center of the screen.

--
Regards,
John

--

Dave Peterson

Excellent! That's what I was looking for.

I always forget to check the ActiveWindow object for solutions (I'm stuck on
Worksheets)

And I agree about not changing the sizing of multiple open windows. What I
intended when saying "to scroll the screen to keep a block of cells near the
center of the screen." was to keep things centered within the "ActiveWindow"
--- we have to keep the user's happy!

--
Regards,
John


"Dave Peterson" wrote:

maybe you could use:

MsgBox ActiveWindow.VisibleRange.Address
or
MsgBox ActiveWindow.VisibleRange.Cells.SpecialCells(xlCel lTypeVisible).Address

But as a user, I wouldn't like what you're trying to do. If I've arranged
multiple windows so I could see stuff. I wouldn't want something to change my
layout.

John Keith wrote:

I need those functions...

I need a way to get the address range of the cells that are currently
showing on the screen for a given worksheet. OR a way to know if a cell or
range of cells are currently "on the screen". Screen resolution and the size
of each cell will have to be taken into account.

I have thought about using API calls to get the screen resolution and then
using .height and .width to get the X,Y,H,W values of every cell's
(rectangle) then from that, calculate which cells are currently showing on
the screen.

Is there a better way?

I am trying to come up with some routines that can be used keep pertinent
data showing on the screen no matter how the user has their screen sizing
set. (with in certain boundaries of course, I.E. at least 15 rows and 4
columns or some preset limit; perhaps an upper limit too) I could also see
using this to know when to scroll the screen to keep a block of cells near
the center of the screen.

--
Regards,
John

--

Dave Peterson