Sorry, it took so long. My e.m. notification's not working again.
That's an interesting approach. It goes to show there's more than one way
to break an egg. I've tested and your solution.
I corrected a problem with it erroring out when it can't find a Pane because
the Active Cell is not Visible. In fact, it doesn't even need to find the
Pane with the ActiveCell. It just needs to go to the lower and / or right
Pane which is
Panes.Count. I also added support for Hidden Rows and / or Columns at the
left and / or upper Pane borders so it doesn't leave you sitting in a Hidden
Row / Column. This required the use of the SpecialCells Function like in
Dave's and / my solution which required adding support for Unprotecting the
Worksheet. Since SpecialCells acts on the whole Worksheet whenever its
source Range is a single Cell, I had to remove the (1) Parameter to the
VisibleRange call and add an exception for the unlikely case when the lower
right Pane is a single Cell. The revised Sub is below.
I've tested it for 1, 2 (horizontal or vertical) and 4 Frozen or Split Panes
and Active Cells Visible, not and Visible Pane Ranges at the Pane borders or
not and Hidden Rows or Columns at the Pane Borders or not. Your solution
requires less code but
it has one drawback. Even with ScreenUpdating set to False, there can often
be a noticible delay (i.e. hourglass seen) of sometimes several seconds on my
P4 2.2 Ghz 480 Mb running XP Pro SP2 and XL 2k, whereas both the Ctrl-Home
Key and Dave's and / my solution have so far always been instantaneous even
if ScreenUpdating is True. It's as if LargeScroll is still simulating the
scroll internally even when ScreenUpdating is False.
On a side note, I was actually able to get "Excel.Application.Sendkeys
Keys:="^{HOME}", Wait:=True" to work in XL 2k by adding a DoEvents call right
afterward which you wouldn't think would be necessary based on the docs on
the Wait Parameter. The "VBA.SendKeys String:="(^{HOME})"" with Wait:=True
or False still won't work even with DoEvents. Since, per Nick Hodge, it's
the VBA, not Excel, version that works in Vista with Excel < 2007, our work
so far hasn't been pointless. Whew again! ;)
Public Sub SelectLoRtPaneUpLfCell2( _
Optional oWksht As Worksheet = Nothing, _
Optional sWkshtPswd As String = "" _
)
Dim bWasAppScnUpdt As Boolean
Dim bWasWkshtProtected As Boolean
Dim oWkshtLoRtCell As Range
Dim lWkshtMaxColNum As Long
Dim lWkshtMaxRowNum As Long
Dim oPane As Pane
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
bWasWkshtProtected = True
.Unprotect sWkshtPswd
End If ' -- .ProtectContents
End With ' -- oWksht
bWasAppScnUpdt = Application.ScreenUpdating
Application.ScreenUpdating = False
Set oPane = ActiveWindow.Panes(ActiveWindow.Panes.Count)
With oPane
lWkshtMaxRowNum = ActiveSheet.Rows.Count
lWkshtMaxColNum = ActiveSheet.Columns.Count
' -- Just use very large numbers that are bound to get to the
top-left of the Pane
.LargeScroll , lWkshtMaxRowNum, , lWkshtMaxColNum
' -- SpecialCells acts on whole Worksheet whenever its source Range
is a single
' -- Cell, so if Visible Range of the lower, rightmost Pane is the
lower
' -- rightmost Cell of the Worksheet (the only time it can be a
single Cell),
' -- just set the Active Cell there.
Set oWkshtLoRtCell = Cells(lWkshtMaxRowNum, lWkshtMaxColNum)
If (.VisibleRange.Address = oWkshtLoRtCell.Address) Then
oWkshtLoRtCell.Select
Else
' -- Move the ActiveCell
.VisibleRange.SpecialCells(xlCellTypeVisible).Cell s(1).Select
End If ' -- Not Lower, Right Pane is single Cell
End With ' -- oPane
Application.ScreenUpdating = bWasAppScnUpdt
If bWasWkshtProtected Then
oWksht.Protect sWkshtPswd
End If ' -- bWasWkshtProtected
End Sub ' -- SelectLoRtPaneUpLfCell2
"NickHK" wrote:
OK, I see what you mean.
However, from a cmd button, SendKeys works in my XL2K/Win2K, as long as
..TakeFocusOnClick for the cmd is set to False.
I take it the aim is to get the top-left visible cell of any panes, with an
arbitrary number of panes and .FreezePanes true or false ?
I thought this might work, but if the panes are frozen, activepane is =
panes.count
With ActiveWindow.ActivePane
.LargeScroll , ActiveSheet.Rows.Count, , ActiveSheet.Columns.Count
.VisibleRange(1).Select
End With
However, this seems to do what you need, although only briefly tested:
Private Sub CommandButton2_Click()
Dim i As Long
Dim pan As Pane
'Make sure the ActiveCell is visible
ActiveCell.Select
'See which Pane contains the ActiveCell
For i = 1 To ActiveWindow.Panes.Count
If Not Intersect(ActiveWindow.Panes(i).VisibleRange, ActiveCell) Is
Nothing Then
Set pan = ActiveWindow.Panes(i)
End If
Next
With pan
'Just use very large numbers that are bound to get to the top-left of
the Pane
.LargeScroll , ActiveSheet.Rows.Count, , ActiveSheet.Columns.Count
'Move the ActiveCell
.VisibleRange(1).Select
End With
End Sub
NickHK
"tc69" wrote in message
...
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.
--------------------- SNIP -------------