Because:
1) SendKeys "^{HOME}", if it worked like it's supposed to, is much easier
than developing and debugging code to do it.
2) ActiveWindow.ActivePane.VisibleRange(1).Select does not reproduce the
Ctrl-Home Key because: a) in cases where you've scrolled the right, lower,
right-upper or right-lower Panes right or down, it goes to the upper / left
-most currently "Visible" Row / Column in the lower and/or right Pane whereas
Ctrl-Home goes to the upper and / or left -most "Un-Hidden, Scollable" Row /
Column (I think upper / left most "Visible" Row / Column is probably a useful
feature but it's not what Marco and I were needing), b) if the leftmost
"Scrollable" Column and / or uppermost "Scrollable" Row in the lower and / or
right Pane is "Hidden" and is "Visible" (if they were "Un-Hidden") in the
current Pane, it leaves you in a "Hidden" Column and / or Row whereas
Ctrl-Home doesn't.
Whew, man you had me going!
I was thinking no way it's that easy after
Dave and I spent all this time writing and debugging a whole VB Sub to do it.
I was so focused on 4-Pane Windows, that right after I posted the enhanced
SelectLoRtPaneUpLfCell Sub below, I realized I also needed it to support
single Pane and dual Pane Windows. I've enhanced it again to do so and to
support Split Windows as well which was pretty easy. I also added optional
Worksheet and Worksheet Password Parameters since the SpecialCells Property
it relies on won't work on Protected Sheets. I don't understand why since it
doesn't appear to ever change anything. Here it is again:
Public Sub SelectLoRtPaneUpLfCell( _
Optional oWksht As Worksheet = Nothing, _
Optional sWkshtPswd As String = "" _
)
' -- If oWksht has Split Windows go to the upper left un-Hidden Cell of
the whole
' -- Worksheet in the Active Pane, otherwise (if Frozen Windows), select
the
' -- upper, left un-Hidden, scrollable Cell of the lower (if just upper
and lower
' -- Panes), right (if just left and right Panes) or lower right (if 4
Panes) Window
' -- Pane of oWksht (or ActiveSheet if it's Nothing). Unprotect and
re-Protect as
' -- necessary using optional sWkshtPswd. oWksht will be Activated when
done.
' -- Duplicates Ctrl-Home Key with VBA without having to use
VBA.SendKeys which
' -- doesn't appear work at all Excel 2000 (on XP Pro) and
Application.SendKeys
' -- whose Wait:=True Parameter doesn't appear to work.
' -- NOTE: In non-Split Windows, if there are no Visible Rows and / or
Columns
' -- in the lower, right Window Pane, Ctrl-Home wraps around to the
first Row
' -- and / or Column which will most likely be non-visible and of course
would be
' -- outside the lower, right Pane. Since this is not very useful, this
Sub will
' -- leave the Active Cell as is and Beep in these cases.
Dim bWkshtWasProtected As Boolean
Dim lUpLfPaneMaxColNum As Long
Dim lUpLfPaneMaxRowNum As Long
Dim lLoRtPaneMinColNum As Long
Dim lLoRtPaneMinRowNum As Long
Dim lWkshtMaxColNum As Long
Dim lWkshtMaxRowNum As Long
Dim oCellRng As Range
Set oCellRng = Nothing
bWkshtWasProtected = False
If oWksht Is Nothing Then
Set oWksht = ActiveSheet
Else
oWksht.Activate
End If
With oWksht
' -- If Worksheet is Protected, Unprotect it so we can use the
' -- SpecialCells Property.
If .ProtectContents Then
bWkshtWasProtected = True
.Unprotect sWkshtPswd
End If ' -- .ProtectContents
lWkshtMaxRowNum = .Rows.Count
lWkshtMaxColNum = .Columns.Count
End With ' -- oWksht
' -- Initialize Lower Right Pane Minimum Row & Column Numbers to invalid
values, so
' -- will generate Nothing Range if not overridden.
lLoRtPaneMinColNum = 0
lLoRtPaneMinRowNum = 0
With ActiveWindow
' -- If Split Windows, always goto upper left un-Hidden Cell of
whole Worksheet.
If Not .FreezePanes Then
lLoRtPaneMinRowNum = 1
lLoRtPaneMinColNum = 1
' -- Else Not Split Windows
Else
With .Panes
With .Item(1).VisibleRange
lUpLfPaneMaxRowNum = .Rows(.Rows.Count).Row
lUpLfPaneMaxColNum = .Columns(.Columns.Count).Column
End With ' -- .Panes(1).VisibleRange
Select Case .Count
' -- If Windows aren't Frozen (i.e. only 1 Pane), go to
upper, left
' -- un-Hidden Cell of Pane # 1 vs. going past its lower
right corner.
Case 1
lLoRtPaneMinRowNum = 1
lLoRtPaneMinColNum = 1
' -- End Case 1
' -- If only 2 Panes, (upper & lower) or (left & right)
Case 2
' -- If left and right Panes only,
If ActiveWindow.SplitRow = 0 Then
lLoRtPaneMinRowNum = 1
lLoRtPaneMinColNum = lUpLfPaneMaxColNum + 1
' -- Else upper & lower Panes only
Else
lLoRtPaneMinRowNum = lUpLfPaneMaxRowNum + 1
lLoRtPaneMinColNum = 1
End If ' -- Else Upper and Lower Panes only
' -- End Case 2
Case 4
' -- If Pane frozen just above & left of lower,
right Cell of the
' -- Active Sheet not including Hidden Rows /
Columns, the
' -- following logic will not work because
SpecialCells returns
' -- Cells for the whole Worksheet in this case!?!)
so force
' -- selection of the lower, right Cell of the
Active Sheet.
If ( _
(lUpLfPaneMaxRowNum = (lWkshtMaxRowNum - 1)) And _
(lUpLfPaneMaxColNum = (lWkshtMaxColNum - 1)) _
) Then
Set oCellRng = Cells(lWkshtMaxRowNum,
lWkshtMaxColNum)
GoTo CLEANUP_AND_EXIT_SUB:
End If
lLoRtPaneMinRowNum = lUpLfPaneMaxRowNum + 1
lLoRtPaneMinColNum = lUpLfPaneMaxColNum + 1
' -- End Case 4
Case Else
' -- 3 or 5+ Panes currently not possible.
GoTo CLEANUP_AND_EXIT_SUB:
' -- End Case Else
End Select ' -- .Count
End With ' -- .Panes
End If ' -- Not .FreezePanes
End With ' -- ActiveWindow
With oWksht
On Error Resume Next
Set oCellRng = _
.Range( _
.Cells( _
lLoRtPaneMinRowNum, _
lLoRtPaneMinColNum), _
.Cells( _
lWkshtMaxRowNum, _
lWkshtMaxColNum) _
) _
.SpecialCells(xlCellTypeVisible).Cells(1)
On Error GoTo 0
End With ' -- oWksht
CLEANUP_AND_EXIT_SUB:
If oCellRng Is Nothing Then
' -- Do nothing
Beep
Else
oCellRng.Select
End If ' -- oCellRng Is Nothing
If bWkshtWasProtected Then
oWksht.Protect sWkshtPswd
End If ' -- bWkshtWasProtected
End Sub ' -- SelectLoRtPaneUpLfCell
