Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Control Home (XL97:SR2)

    It has been one of those days....Does anyone know the code equivalent to "Ctrl-Home"?

    I've tried Application.SendKeys "{Home}" without any luck.

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    Try:

    <pre> ActiveSheet.Range("A1").Select
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    Legare,

    Sometimes row 1 will be hidden therefore your suggestion will not work. If any rows are hidden the "Ctrl-Home" brings the user to the upper left hand cell.

    John

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    This almost does what you want,

    <font color=red>ActiveSheet.Cells.SpecialCells(xlCellTyp eVisible).Cells(1, 1).Activate</font color=red>

    but it doesn't do it quite right for split and/or frozen panes - this might help you in that scenario:

    <font color=red>ActiveWindow.ActivePane.ScrollColumn = 1
    ActiveWindow.ActivePane.ScrollRow = 1</font color=red>

    but I can't then work out how to select the top left visible cell of the pane.

    <font color=red>ActiveSheet.Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Activate</font color=red>

    should work - but doesn't if you've got hidden rows.

    I must admit, I thought this was an easy problem, but you've got me stumped!

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    Welcome to my world....One would have thought this would be easy.

    John

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    Try

    x = ActiveWindow.Panes.Count
    ActiveWindow.Panes(x).VisibleRange.Cells(1).Activa te

    which should behave the same as Ctrl-Home for windows with frozen panes.

    Andrew C

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    actually the simpler

    ActiveWindow.VisibleRange.Cells(1).Activate

    seems to work just as well.

    Andrew C

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    But what happens if the visiblerange starts at, say, x250?

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    Brooke,

    I think the same thing that happens when you press Ctrl-Home.

    Have you experienced something different ?

    Andrew

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    yes, i have. For me, control home takes the active cell to the first cell of the activesheet's used range that is not hidden - ie normally a1. the only time it doesn't do this is when the panes are split or frozen, in which case it goes to the first cell that has it's visible property = true in that pane. with split windows this is pretty much the same as above but not with frozen panes.

    With your code above on my system, the first cell in the visiblerange is selected. However, visible range is a property of the window, not the sheet as specialcells(xlcelltypevisible) is.

    so on my system, if I have an unsplit and unfrozen sheet, where x250 is the top left cell I can see, your code takes me to x250, whereas control home takes me to a1.

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Control Home (XL97:SR2)

    How about:

    ActiveSheet.Range("A1").Select
    ActiveWindow.VisibleRange.Cells(1).Activate

    This combines Legare's and Andrew's solutions and seems to do what I think you want.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    <hr>seems to do what I think you want.<hr>
    thanks macropod - it's not I want but what John wants - and that is code to replicate the behaviour of control-home. Your suggestion works fine unless the window is frozen and scrolled. Maybe John should let us know whether this aspect is important to him?

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    <hr>I've tried Application.SendKeys "{Home}" without any luck.<hr>
    I may be picking up on a typo here but have you tried:

    Application.SendKeys "^{HOME}" - note the caret

    This works perfectly for me. Note that the excel application must have the focus for this to work - so it doesn't work if I'm testing it from either the code pane or the immediate window in the vbe, but only if I call it from the run macro button in excel.

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    I did try using the caret "^" in the line of code but it did not work. I attached the code to a button and it did finally work. Unfortunately this brings up the question why it only works attached to a button.

    John

  15. #15
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Home (XL97:SR2)

    I must admit here that I have never used sendkeys before this thread. I've never had the need to and there is a perceived wisdom that it's best to avoid them if possible, so I've always shied away. Note my point that excel must have the focus, not the vbe window. How were you calling it before?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •