I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson

I might use sendkeys in this case.

But if you want...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long
Dim myCell As Range

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

Set myCell = Nothing
On Error Resume Next
With ActiveSheet
Set myCell = .Range(.Cells(myRow + 1, myCol + 1), _
.Cells(.rows.Count, .Columns.Count)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
End With
On Error GoTo 0

If myCell Is Nothing Then
'do nothing
Beep
Else
myCell.Select
End If
End Sub

Marco wrote:

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson


--

Dave Peterson

But the SendKeys is not working. Do I have the correct syntax? What could
be some of the variations I can use?

I'll try the code below, just thinking ahead.

Thanks again.

"Dave Peterson" wrote:

I might use sendkeys in this case.

But if you want...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long
Dim myCell As Range

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

Set myCell = Nothing
On Error Resume Next
With ActiveSheet
Set myCell = .Range(.Cells(myRow + 1, myCol + 1), _
.Cells(.rows.Count, .Columns.Count)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
End With
On Error GoTo 0

If myCell Is Nothing Then
'do nothing
Beep
Else
myCell.Select
End If
End Sub

Marco wrote:

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson


--

Dave Peterson

I took out the space character and it worked ok:

SendKeys "(^{HOME})"



Marco wrote:

But the SendKeys is not working. Do I have the correct syntax? What could
be some of the variations I can use?

I'll try the code below, just thinking ahead.

Thanks again.

"Dave Peterson" wrote:

I might use sendkeys in this case.

But if you want...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long
Dim myCell As Range

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

Set myCell = Nothing
On Error Resume Next
With ActiveSheet
Set myCell = .Range(.Cells(myRow + 1, myCol + 1), _
.Cells(.rows.Count, .Columns.Count)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
End With
On Error GoTo 0

If myCell Is Nothing Then
'do nothing
Beep
Else
myCell.Select
End If
End Sub

Marco wrote:

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Do you mean to get to the upper left cell of the Pane containing the active
cell? If so, try

Dim P As Pane
Dim N As Long
If ActiveSheet.Type = xlWorksheet Then
For N = 1 To ActiveWindow.Panes.Count
Set P = ActiveWindow.Panes(N)
If Not Application.Intersect(ActiveCell, P.VisibleRange) Is Nothing
Then
P.VisibleRange(1, 1).Select
Exit For
End If
Next N
End If


Otherwise, try

If ActiveSheet.Type = xlWorksheet Then
ActiveWindow.Panes(1).VisibleRange.Cells(1, 1).Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Marco" wrote in message
...
I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

It would be the upper left cell under and to the left of the frozen
rows/column.

I tried both codes you provided and the second one takes me to the cell next
to the labels [the equivalent to A1 on a non-frozen sheet]. The other code
gave me an error at the "If not ...." line.

Thanks for your help.

"Chip Pearson" wrote:

Do you mean to get to the upper left cell of the Pane containing the active
cell? If so, try

Dim P As Pane
Dim N As Long
If ActiveSheet.Type = xlWorksheet Then
For N = 1 To ActiveWindow.Panes.Count
Set P = ActiveWindow.Panes(N)
If Not Application.Intersect(ActiveCell, P.VisibleRange) Is Nothing
Then
P.VisibleRange(1, 1).Select
Exit For
End If
Next N
End If


Otherwise, try

If ActiveSheet.Type = xlWorksheet Then
ActiveWindow.Panes(1).VisibleRange.Cells(1, 1).Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Marco" wrote in message
...
I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

I've tried the following combos of VBA and Excel 2000 v9.0.2720 Application.
SendKeys on XP Pro 2002 SP 2 to no avail:

' -- Go to upper left corner of lower right Frozen Pane before saving
Workbook.

' -- None of the following works!?!
SendKeys String:="^{HOME}", Wait:=True
SendKeys String:="(^{HOME})", Wait:=True
SendKeys String:="(^){HOME}", Wait:=True
SendKeys String:="{HOME}", Wait:=True
SendKeys String:="{RIGHT}", Wait:=True
SendKeys String:="{right}", Wait:=True
SendKeys String:="{home}", Wait:=False

' -- This works but only after the Macro has ended in spite of
Wait:=True!?!
Application.SendKeys Keys:="(^{HOME})", Wait:=True

Save

I noticed the following post from Nick Hodge which indicates VBA's version
will work with more OS / Excel combos but I'm not getting VBA's version to
work at all and only partially with Excel's version.

Per Nick Hodge on 3/22/2007 11:11 PM PST in microsoft.public.excel.charting:

"Caution: You may find if using this on Windows Vista with Excel versions
before Excel2007 that the Excel method will not work with Vista's UAC (User
Access Control) switched on (default). The VBA statement will work. Both
will work in Excel 2007 (go figure)"

I've modified Dave Peterson's VBA Sub that emulates Ctrl-Home Key to support
Hidden Rows / Columns, Frozen Panes not anchored at the Worksheet's upper,
left corner and even to handle the very unlikely exception of the lower,
right Cell being the only Cell in the lower, right Pane.

Public Sub SelectLoRtPaneUpLfCell()

' -- Select the upper, left Cell of the lower, right Window Pane of the
Active Sheet
' -- i.e. duplicates Ctrl-Home Key with VBA without having to use
VBA.SendKeys which
' -- doesn't appear work at all and Application.SendKeys whose
Wait:=True Parameter
' -- doesn't appear to work.

' -- NOTE: If there are no Visible Rows and / or Columns in the lower,
right Window
' -- Pane, Ctrl-Home wraps around to the first Rows 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 lUpLfPaneMaxColNum As Long
Dim lUpLfPaneMaxRowNum As Long
Dim lWkshtMaxColNum As Long
Dim lWkshtMaxRowNum As Long
Dim oCellRng As Range

With ActiveWindow.Panes(1).VisibleRange
lUpLfPaneMaxRowNum = .Rows(.Rows.Count).Row
lUpLfPaneMaxColNum = .Columns(.Columns.Count).Column
End With ' -- ActiveWindow.Panes(1).VisibleRange

With ActiveSheet
lWkshtMaxRowNum = .Rows.Count
lWkshtMaxColNum = .Columns.Count

' -- If Pane frozen just above & left of lower, right Cell of whole
Worksheet
' -- not including Hidden Rows / Columns, following logic will not
work
' -- (because SpecialCells returns Cells for whole Worksheet in
this case!?!)
' -- so force selection of lower, right Cell of whole Worksheet.
If ( _
(lUpLfPaneMaxRowNum = (lWkshtMaxRowNum - 1)) And _
(lUpLfPaneMaxColNum = (lWkshtMaxColNum - 1)) _
) Then
Cells(lWkshtMaxRowNum, lWkshtMaxColNum).Select
Exit Sub
End If

Set oCellRng = Nothing
On Error Resume Next
Set oCellRng = _
.Range( _
.Cells( _
lUpLfPaneMaxRowNum + 1, _
lUpLfPaneMaxColNum + 1), _
.Cells( _
lWkshtMaxRowNum, _
lWkshtMaxColNum)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
On Error GoTo 0
End With ' -- ActiveSheet

If oCellRng Is Nothing Then
' -- Do nothing
Beep
Else
oCellRng.Select
End If ' -- oCellRng Is Nothing

End Sub ' -- SelectLoRtPaneUpLfCell



"Dave Peterson" wrote:

I took out the space character and it worked ok:

SendKeys "(^{HOME})"



Marco wrote:

But the SendKeys is not working. Do I have the correct syntax? What could
be some of the variations I can use?

I'll try the code below, just thinking ahead.

Thanks again.

"Dave Peterson" wrote:

I might use sendkeys in this case.

But if you want...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long
Dim myCell As Range

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

Set myCell = Nothing
On Error Resume Next
With ActiveSheet
Set myCell = .Range(.Cells(myRow + 1, myCol + 1), _
.Cells(.rows.Count, .Columns.Count)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
End With
On Error GoTo 0

If myCell Is Nothing Then
'do nothing
Beep
Else
myCell.Select
End If
End Sub

Marco wrote:

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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 -------------

Sorry to intrude on your programming conversation, but I picked up your link on a google search - your discussion seems to revolve around a similar issue that I can't resolve with my limited VB experience.

Not quite sure if I'll get a response, but here goes:

SendKeys ("^{HOME}") works fine in emulating the Ctrl-Home keystroke. However if you use it within a routine to determine the required ctrl-home cell address, it fails, as it only positions the cursor once the routine is complete and active control is passed back to the user.

Is there any way of inserting code into a routine that would determine the ctrl-home cell position of the active sheet?

Your expert input would be most appreciated.

Thanks, Charles.



EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

If Not ActiveWindow.FreezePanes Then
Cells(1).Select
Else
Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Select
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<charles wrote in message ...
Sorry to intrude on your programming conversation, but I picked up your
link on a google search - your discussion seems to revolve around a
similar issue that I can't resolve with my limited VB experience.

Not quite sure if I'll get a response, but here goes:

SendKeys ("^{HOME}") works fine in emulating the Ctrl-Home keystroke.
However if you use it within a routine to determine the required ctrl-home
cell address, it fails, as it only positions the cursor once the routine
is complete and active control is passed back to the user.

Is there any way of inserting code into a routine that would determine the
ctrl-home cell position of the active sheet?

Your expert input would be most appreciated.

Thanks, Charles.



EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Right off the top of my head, that won't work if you have Hidden Columns /
Rows at the Pane borders (which you probably won't in most cases). If you
want to determine Ctrl-Home Active Cell position "all" cases, you basically
have to do what my Sub does to determine where to move the ActiveCell. Trust
me, I've tested pretty well tested the possible scenarios.

"Bob Phillips" wrote:

If Not ActiveWindow.FreezePanes Then
Cells(1).Select
Else
Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Select
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<charles wrote in message ...
Sorry to intrude on your programming conversation, but I picked up your
link on a google search - your discussion seems to revolve around a
similar issue that I can't resolve with my limited VB experience.

Not quite sure if I'll get a response, but here goes:

SendKeys ("^{HOME}") works fine in emulating the Ctrl-Home keystroke.
However if you use it within a routine to determine the required ctrl-home
cell address, it fails, as it only positions the cursor once the routine
is complete and active control is passed back to the user.

Is there any way of inserting code into a routine that would determine the
ctrl-home cell position of the active sheet?

Your expert input would be most appreciated.

Thanks, Charles.



EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com