Check if a certain row is displayed

[5orin]

Hello

I have been trying to find some kind of function in vbscript which checkes if a specific row is currently being displayed or not. By that I don't mean if it is hidden or not, but if the row is on the screen. For example, if a user is viewing rows 25 - 80, then row 200 is not on display.

Is there any built-in function in Excel 2007, or is there a way to write one myself?

Thanks

Sorin

 

[tsuji]

You can use application's windows(1) object to determine the visible screen's various properties.
[tt]
'suppose oxl (in vbs) or Application in macro environment be the excel application object, loaded with target objects workbook/sheet/cell being meaningful.

set owin=oxl.windows(1) 'the current screen visible
x_topleft=owin.visiblerange.cells.row 'topleft cell row coordinate
y_topleft=owin.visiblerange.cells.column 'topleft cell column coorrdinate
x_span=owin.visiblerange.rows.count
y_span=owin.visiblerange.columns.count

x_active=oxl.activecell.row 'active cell's row corrdinate
y_active=oxl.activecell.column 'active cell's column coordinate

if (x_active>x_topleft) and (x_active<(x_topleft+x_span)) and _
(y_active>y_topleft) and (y_active<(y_topleft+y_span)) then
msgbox "active cell is in the visible screen."
else
msgbox "activecell is not in the visible screen."
end if
[/tt]

 

[tsuji]

amendment
upon re-read what I posted, sure, two of the conditions should be read >= instead of > only.
[tt]
if (x_active>[highlight]=[/highlight]x_topleft) and (x_active<(x_topleft+x_span)) and _
(y_active>[highlight]=[/highlight]y_topleft) and (y_active<(y_topleft+y_span)) then
msgbox "active cell is in the visible screen."
else
msgbox "activecell is not in the visible screen."
end if
[/tt]