Cell Visibility Algorithm

[Neal Zimm]

Hi All,
I'm having difficulty finding out about the more 'technical' screen
properties.

I'm building this function to show the user a selected cell on a Ws
if it's on the screen and not in a hidden column.
I don't really want to unhide the column or scroll the display.

Sometimes a plain msg box, and others with a response.

For one worksheet type I know what columns will typically be 'on
the screen.'

What is a way to 'calculate' if a cell appears on the screen if
it's known that it's NOT in a hidden column ??

The code section below is marked: ???? HOW TO

Thanks,
Neal Z.


Public Function CellMsgValueF(CellRng As Range, Msg As String, _
Title As String, _
Optional bSelectCell As Boolean = False, _
Optional Button As Long = vbInformation) As String

' If Ws name passes mask and cell is hidden, add to Msg.
' If cell is not hidden and is 'on the screen', select the cell + show Msg.

Dim OldSelect As Range
Dim WsNa As String
Dim SaveColor As Integer
Dim bCellIsSeen As Boolean, bASU As Boolean


bASU = SCRNonF
EventsOFF
WsNa = ActiveSheet.Name

If WsNa Like gRmRteMsk Then 'will have hidden cols

Title = "Route " & WsNa

If Columns(DataRng.Column).Hidden = True Then

Msg = Msg & Cr2 & "Info Type," & Tb & RmRtColNaAy(DataRng.Column) _
& Cr & " Value," & Tb & DataRng.Value

ElseIf DataRng.Column <= iColX Then

bCellIsSeen = True

Else ' ???? HOW TO TELL IF CELL IS VISIBLE ON SCREEN.
'IF NOT HIDDEN, I DON'T WANT TO SCROLL THE SCREEN.
'OTHER COLUMNS MAY/MAY NOT BE HIDDEN AFFECTING WHAT'S ON SCREEN.
End If

Else ' SAME QUESTION AS ABOVE for different Ws's.

End If


If bCellIsSeen and bSelectCell Then
Set OldSelect = Selection
With CellRng
SaveColor = .Interior.ColorIndex
.Interior.ColorIndex = LightYellow 'a public constant
.Select
End With
End If


If Button Mod 16 = 0 And Button < 49 Then
MsgBox Msg, Button, Title
Else
CellMsgValueF = MsgBox(Msg, Button, Title)
End If


If bCellIsSeen and bSelectCell Then
CellRng.Interior.ColorIndex = SaveColor
OldSelect.Select
End If

EventsON
Call SCRNback(bASU)

End Function

 

[Jim Cone]

Neal,
ActiveWindow.VisibleRange returns a range object that represents the
cells visible in the window.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neal Zimm" <[email protected]>
wrote in message
Hi All,
I'm having difficulty finding out about the more 'technical' screen
properties.
I'm building this function to show the user a selected cell on a Ws
if it's on the screen and not in a hidden column.
I don't really want to unhide the column or scroll the display.
Sometimes a plain msg box, and others with a response.
For one worksheet type I know what columns will typically be 'on
the screen.'
What is a way to 'calculate' if a cell appears on the screen if
it's known that it's NOT in a hidden column ??
The code section below is marked: ???? HOW TO
Thanks,
Neal Z.

-snip-

 

[Neal Zimm]

Egads,
Thanks Jim, never thought to look @ activewindow properties, live and
learn.
Neal