Cursor to upper left of frozen cells (in macro)

[Mike]

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!

 

[Shane Devenshire]

Hi,

Look at the sendkeys command, here is some code:

SendKeys "^{Home}"

 

[Sheeloo]

Application.ActiveWindow.ScrollRow
and
Application.ActiveWindow.ScrollColumn
will give you the row and column numbers which you can use in your code...

 

[Mike]

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

 

[Sheeloo]

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]

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]

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

 

[Mike]

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]

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

 

[Mike]

Perfect! Thanks so much--y'all are always such a huge help.

 

[Gord Dibben]

Thanks Mike.

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


Gord

 

[vivek kapadia]

Thanks alot Mike...keep up the good work..