[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

SendKeys "(^ {HOME})"

marco

12/16/2006 12:15:00 AM

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

Dave Peterson

12/16/2006 1:15:00 AM

0

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

marco

12/16/2006 1:53:00 AM

0

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

12/16/2006 2:03:00 AM

0

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

marco

12/16/2006 2:53:00 AM

0

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

12/16/2006 3:08:00 AM

0

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

Chip Pearson

12/17/2006 12:24:00 AM

0

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" <Marco@discussions.microsoft.com> wrote in message
news:68F46C55-A306-4AA2-A623-E801C765A560@microsoft.com...
>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


marco

12/17/2006 3:43:00 PM

0

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" <Marco@discussions.microsoft.com> wrote in message
> news:68F46C55-A306-4AA2-A623-E801C765A560@microsoft.com...
> >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
>
>
>