Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

RE: Cursor to upper left of frozen cells (in macro)

49 views
Skip to first unread message

Sheeloo

unread,
Mar 4, 2009, 11:08:14 AM3/4/09
to
Application.ActiveWindow.ScrollRow
and
Application.ActiveWindow.ScrollColumn
will give you the row and column numbers which you can use in your code...

"Shane Devenshire" wrote:

> Hi,
>
> Look at the sendkeys command, here is some code:
>
> SendKeys "^{Home}"
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Mike" wrote:
>
> > I have crudely written a macro that takes the cursor to A1 on all worksheets
> > in a workbook. I'm hoping to add a command that will send the curso to the
> > upper left of any frozen cells first, then go to A1 (basically I want it to
> > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> > code I am currently using.
> >
> > Dim wk As Worksheet
> > For Each wk In ActiveWorkbook.Worksheets
> > wk.Activate
> > Range("A1").Select
> > Next wk
> >
> > Sheets(1).Activate
> >
> > Thanks for the help!

Sheeloo

unread,
Mar 4, 2009, 4:27:01 PM3/4/09
to
Sub ctrlHome()
Cells(Application.ActiveWindow.ScrollRow, _
Application.ActiveWindow.ScrollColumn).Select
End Sub

will take you to the tol-left cell below the frozen pane...
that is what you wanted, right?

Basically Application.ActiveWindow.ScrollRow gives you the rowno and
Application.ActiveWindow.ScrollColumn gives you the columnno of the first
cell...


"Mike" wrote:

> SendKeys isn't working for me.. Sheeloo, can you give me an example of how
> to use those commands in my code?

Mike

unread,
Mar 4, 2009, 4:40:01 PM3/4/09
to
Thanks for the help, but it's still not doing what I want. That does take me
to the top-left cell of the frozen pane, but what I really want is to take
the cursor to the first frozen cell (where it would take you if you pressed
Ctrl+Home).

SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding
shift I highlight everything from the "Home" cell to A1... can I fix this?

Gord Dibben

unread,
Mar 4, 2009, 5:36:24 PM3/4/09
to
I'm confused.

When you manually hit CTRL + Home which cell becomes active?

Should be the top left cell below the "Frozen" area.

i.e. rows 1:5 are frozen.

CTRL + Home will select A6

Rows 1:5 and columns A:C are frozen.

CTRL + Home will select D6

Sheeloo's code does that.

Where do you want to go?


Gord Dibben MS Excel MVP

On Wed, 4 Mar 2009 13:40:01 -0800, Mike <Mi...@discussions.microsoft.com>
wrote:

Mike

unread,
Mar 5, 2009, 9:27:01 AM3/5/09
to
When I use Sheeloo's code, it takes me to the top left frozen cell *of the
cells I can currently see on my screen*... Not the top left of the frozen
cells as in your example below.

Ideally I would like to put a line in the code below that would take the
cursor (and thereby the view on the screen) to the top left frozen cell, and
then leave the cursor on A1. And repeat for each sheet. The idea is to use
this macro before saving and closing a workbook so that when it is opened
next, the user sees continuous both down and right starting at A1.

Sub Home()

Dim wk As Worksheet
For Each wk In ActiveWorkbook.Worksheets
wk.Activate
Range("A1").Select
Next wk

Sheets(1).Activate

End Sub

Right now, it just goes to A1, but, for example if colums 1:3 are frozen,
the result could be to see colums 1:3 and then 30:50ish if the previous user
was working in the lower range.

Thanks a lot for the help,
Mike

Gord Dibben

unread,
Mar 5, 2009, 2:44:01 PM3/5/09
to
Rows 1:3 are frozen.

User has scrolled down so 1:3 are visible and rows 30:59 are visible

You want the view changed from seeing rows 1:3 and rows 30:59 to continuous
rows 1:32 ?

Application.Goto Range("A1"), Scroll:=True


Gord

On Thu, 5 Mar 2009 06:27:01 -0800, Mike <Mi...@discussions.microsoft.com>

Mike

unread,
Mar 5, 2009, 5:48:01 PM3/5/09
to
Perfect! Thanks so much--y'all are always such a huge help.

Gord Dibben

unread,
Mar 5, 2009, 9:00:32 PM3/5/09
to
Thanks Mike.

Took me a while to find out what you wanted but we got there.


Gord

On Thu, 5 Mar 2009 14:48:01 -0800, Mike <Mi...@discussions.microsoft.com>

vivek kapadia

unread,
Sep 10, 2010, 1:59:06 AM9/10/10
to
Thanks alot Mike...keep up the good work..

> On Wednesday, March 04, 2009 10:15 AM Mik wrote:

> I have crudely written a macro that takes the cursor to A1 on all worksheets
> in a workbook. I'm hoping to add a command that will send the curso to the
> upper left of any frozen cells first, then go to A1 (basically I want it to
> perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> code I am currently using.
>
> Dim wk As Worksheet
> For Each wk In ActiveWorkbook.Worksheets
> wk.Activate
> Range("A1").Select
> Next wk
>
> Sheets(1).Activate
>
> Thanks for the help!


>> On Wednesday, March 04, 2009 10:48 AM shanedevenshir wrote:

>> Hi,
>>
>> Look at the sendkeys command, here is some code:
>>
>> SendKeys "^{Home}"
>>
>> --
>> If this helps, please click the Yes button.
>>
>> Cheers,
>> Shane Devenshire
>>
>>
>> "Mike" wrote:


>>> On Wednesday, March 04, 2009 11:08 AM Click above to get my email id wrote:

>>> Application.ActiveWindow.ScrollRow
>>> and
>>> Application.ActiveWindow.ScrollColumn
>>> will give you the row and column numbers which you can use in your code...
>>>
>>>
>>>
>>> "Shane Devenshire" wrote:


>>>> On Wednesday, March 04, 2009 3:33 PM Mik wrote:

>>>> SendKeys is not working for me.. Sheeloo, can you give me an example of how


>>>> to use those commands in my code?
>>>>
>>>> "Sheeloo" wrote:


>>>>> On Wednesday, March 04, 2009 4:27 PM Click above to get my email id wrote:

>>>>> Sub ctrlHome()
>>>>> Cells(Application.ActiveWindow.ScrollRow, _
>>>>> Application.ActiveWindow.ScrollColumn).Select
>>>>> End Sub
>>>>>
>>>>> will take you to the tol-left cell below the frozen pane...
>>>>> that is what you wanted, right?
>>>>>
>>>>> Basically Application.ActiveWindow.ScrollRow gives you the rowno and
>>>>> Application.ActiveWindow.ScrollColumn gives you the columnno of the first
>>>>> cell...
>>>>>
>>>>>
>>>>> "Mike" wrote:


>>>>>> On Wednesday, March 04, 2009 4:40 PM Mik wrote:

>>>>>> Thanks for the help, but it's still not doing what I want. That does take me
>>>>>> to the top-left cell of the frozen pane, but what I really want is to take
>>>>>> the cursor to the first frozen cell (where it would take you if you pressed
>>>>>> Ctrl+Home).
>>>>>>
>>>>>> SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding
>>>>>> shift I highlight everything from the "Home" cell to A1... can I fix this?
>>>>>>
>>>>>> "Sheeloo" wrote:


>>>>>>> On Wednesday, March 04, 2009 5:36 PM Gord Dibben wrote:

>>>>>>> I'm confused.
>>>>>>>
>>>>>>> When you manually hit CTRL + Home which cell becomes active?
>>>>>>>
>>>>>>> Should be the top left cell below the "Frozen" area.
>>>>>>>
>>>>>>> i.e. rows 1:5 are frozen.
>>>>>>>
>>>>>>> CTRL + Home will select A6
>>>>>>>
>>>>>>> Rows 1:5 and columns A:C are frozen.
>>>>>>>
>>>>>>> CTRL + Home will select D6
>>>>>>>
>>>>>>> Sheeloo's code does that.
>>>>>>>
>>>>>>> Where do you want to go?
>>>>>>>
>>>>>>>
>>>>>>> Gord Dibben MS Excel MVP
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Wed, 4 Mar 2009 13:40:01 -0800, Mike <Mi...@discussions.microsoft.com>
>>>>>>> wrote:


>>>>>>>> On Thursday, March 05, 2009 9:27 AM Mik wrote:

>>>>>>>> When I use Sheeloo's code, it takes me to the top left frozen cell *of the
>>>>>>>> cells I can currently see on my screen*... Not the top left of the frozen
>>>>>>>> cells as in your example below.
>>>>>>>>
>>>>>>>> Ideally I would like to put a line in the code below that would take the
>>>>>>>> cursor (and thereby the view on the screen) to the top left frozen cell, and
>>>>>>>> then leave the cursor on A1. And repeat for each sheet. The idea is to use
>>>>>>>> this macro before saving and closing a workbook so that when it is opened
>>>>>>>> next, the user sees continuous both down and right starting at A1.
>>>>>>>>
>>>>>>>> Sub Home()
>>>>>>>>
>>>>>>>> Dim wk As Worksheet
>>>>>>>> For Each wk In ActiveWorkbook.Worksheets
>>>>>>>> wk.Activate
>>>>>>>> Range("A1").Select
>>>>>>>> Next wk
>>>>>>>>
>>>>>>>> Sheets(1).Activate
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Right now, it just goes to A1, but, for example if colums 1:3 are frozen,
>>>>>>>> the result could be to see colums 1:3 and then 30:50ish if the previous user
>>>>>>>> was working in the lower range.
>>>>>>>>
>>>>>>>> Thanks a lot for the help,
>>>>>>>> Mike
>>>>>>>>
>>>>>>>> "Gord Dibben" wrote:


>>>>>>>>> On Thursday, March 05, 2009 2:44 PM Gord Dibben wrote:

>>>>>>>>> Rows 1:3 are frozen.
>>>>>>>>>
>>>>>>>>> User has scrolled down so 1:3 are visible and rows 30:59 are visible
>>>>>>>>>
>>>>>>>>> You want the view changed from seeing rows 1:3 and rows 30:59 to continuous
>>>>>>>>> rows 1:32 ?
>>>>>>>>>
>>>>>>>>> Application.Goto Range("A1"), Scroll:=True
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Gord
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Thu, 5 Mar 2009 06:27:01 -0800, Mike <Mi...@discussions.microsoft.com>
>>>>>>>>> wrote:


>>>>>>>>>> On Thursday, March 05, 2009 5:48 PM Mik wrote:

>>>>>>>>>> Perfect! Thanks so much--y'all are always such a huge help.
>>>>>>>>>>
>>>>>>>>>> "Gord Dibben" wrote:


>>>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>>>>> Win a free Professional license of CodeSmith Generator 5.2!
>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/32aeed64-8518-4bd0-9614-e82b5d3ab3a4/win-a-free-professional-license-of-codesmith-generator-52.aspx

0 new messages