CTRL + HOME in VB6 - not VBA

Hi,

I have to go do hit "CTRL + HOME" from code. How do I do that?

I have tried already "range("a1").select" but it does not do the same since I have a freeze panes (from code I do not know exactly).

Is there a way to do that?

- George

Hi George,

this works for me:
Dim thisPane As Pane: Set thisPane = Application.ActiveWindow.ActivePane
Dim thisSheet As Worksheet: Set thisSheet = Application.ActiveSheet
With thisPane
Call .LargeScroll(Down:=1, ToRight:=1)
Call .ScrollIntoView(0, 0, 0, 0)
thisSheet.Cells(.VisibleRange.Row, .VisibleRange.Column).Activate
End With

(replace the Active... object references as needed)

It's not particularly nice code, though. It just scrolls the pane to the top-left and then selects the top-left-most visible cell, which translates into selecting the top-left cell. I had to put in the .LargeScroll because .ScrollIntoView doesn't seem to work as advertised if there's a frozen pane in one direction but not in the other. Ick.

Thanks,

I hate to use the activeWindow object from within VB6, but if I have no other way I will...

If there is away to know where is the current freeze panes set, I can remove the panes go to A1 and freeze them again...

just sharing my thoughts..

- George

How about:
appExel.goto TargetSheet.Cells(1)

I hate to use the activeWindow object from within VB6, but if I have no other way I will...
I don't see that you have a choice. ;)
No matter which solution you end up with, because what action the Ctrl+Home command produces depends on precisely that - which window currently is active. Even if you want to simulate first activating a specific sheet and then giving the command, you'll still have to call the .Activate method of the sheet before you call that of the cell, I think.
If there is away to know where is the current freeze panes set, I can remove the panes go to A1 and freeze them again...
I tried that, and it doesn't produce the effect you want. The selected cell ends up being A1 in the frozen pane, not the top-left cell in the unfrozen pane. You can use the Windows object's .SplitRow and .SplitColumn properties to get the location of the pane boundary, though, so that'll cut out the ActivePane object and give you a better way to locate the top-left cell in the unfrozen pane.
appExel.goto TargetSheet.Cells(1)
Isn't that the same as .Range("A1")? I've never worked with panes in code before, but from what I've seen in the Help so far it looks to me as if the sheet doesn't know anything about the windows and panes that it's displayed in, so one has to approach this using the .Windows object tree of the Application or Workbook, rather than the sheet object...



ETA: So, putting the things I just said together, my revised suggestion is
Call Sheet.Activate
With Application.ActiveWindow
Call Sheet.Cells(.SplitRow + 1, .SplitColumn + 1).Activate
End With

Still two ActiveWindow references in there, but as I said I don't think you can do without in this situation, or rather, it makes no sense to try and retrieve the window object differently in this situation since the line before in effect explicitly assigns the ActiveWindow.

How about:
appExel.goto TargetSheet.Cells(1)
I tested it and I think it is working! I do not understand why though!

thanks!

Really? It always goes to A1 for me, no matter how I set up the panes. :confused:

Congrats on 1600 posts, by the way!! :cool:

Really? It always goes to A1 for me, no matter how I set up the panes. :confused:

I still do not understand why. I am on Office 2003. I will test this on 2000 tomorrow

Congrats on 1600 posts, by the way!! :cool:
I did not notice that! thanks! I used to contribute a lot more in the past, but now I am busier :/

Sorry, didn't read the question properly :whoops:

George, I suspect your pane arrangement might have happened to coincide with the result of my speculative suggestion?

I did come up with a long-winded alternative, but the Cas's more elegant solution is just as effective and can be modified to be explicit using the same approach that I have:

With ThisWorkbook.Windows(1)
Select Case True
'columns + rows frozen
Case .Panes.Count = 4
.VisibleRange.Cells(.VisibleRange.Cells.Count).Offset(1, 1).Activate
'rows frozen
Case .Panes(1).VisibleRange.Columns.Count = .VisibleRange.Columns.Count

With .Panes(1).VisibleRange
.Parent.Rows(.Rows.Count).Offset(1, 0).Cells(1).Activate
End With

'columns frozen
Case .Panes(1).VisibleRange.Rows.Count = .VisibleRange.Rows.Count

With .Panes(1).VisibleRange
.Parent.Columns(.Columns.Count).Offset(0, 1).Cells(1).Activate
End With

End Select

End With

You can refer to the Window directly using the name of the Workbook in place of the index integer, however the Pane index always refers to the fixed pane regardless of your setup.

Your first solution does work on office 2003! However, it does not work on 2000! I just tested it there!
my code was objExlApp.GoTo objExlApp.activesheet.Cells(1)
objExlApp.Range("A1").Select
I just found out that it worked when A1 to H1 were merged (that was the file that I was testing on)! I need to test more. I hope I can give you a better precise result tomorrow.

Thanks again for your reply.

You can refer to the Window directly using the name of the Workbook in place of the index integer
However, you have to activate the window before activating the cell, otherwise the latter produces an RTE. So, no matter what, this seems to be one case where ActiveWindow is completely appropriate.
Select Case True
Woohoo, a fellow "Select Case True" user! :)
What's your personal preference for the catch-all case, "Case Else" or "Case True"?

Another method that may give you what you are looking for
objExlApp.SendKeys("^{Home}")

What's your personal preference for the catch-all case, "Case Else" or "Case True"?
Has to be 'Case Else', you get enough flak using 'Select Case True'! :D

Has to be 'Case Else'
I used to use "Else" as well, but have recently switched to "True". Makes more sense to me and is yet another visual queue to distinguish it from a "standard" Select Case.
you get enough flak using 'Select Case True'! :D
Yeah, one keeps reading rants against it. *shrug*

Another method that may give you what you are looking for
objExlApp.SendKeys("^{Home}")
Thanks for the suggestion but it did not work! I guess because the window is not vivible.

I would like to thank you again Cas & Timbo. I have modefied the codes you gave me (since I am in VB6 not VBA) and the following command is working on Office 2003.
With objExlApp.Application.ActiveWindow
Call objExlApp.Cells(.SplitRow + 1, .SplitColumn + 1).Activate
End With
I would like to test it on 2000 too. I will update you.

Thanks again,

- George