Excel - Get cursor to stop in TOP-left-cell of screen 3

[DaleWatson123321]

Something simple - yet so difficult.

Objective: Using VBA, have the cursor stop at a cell located in the TOP-left-corner of the current screen.

Using manual methods, this is not a problem. For example by using the &quot;Go To&quot; function, or by <End> + <Up Arrow>.

Problem: In using VBA, these manual methods are of no use. The SAME methods that work manually do NOT work using VBA. The result is that the destination cell is located in the CENTER-left of the screen instead of the TOP-left-corner.

Has anyone been able to come up with a VBA solution ?

Your solution would be most appreciated.

Thanks. ...Dale Watson

 

[rmikesmith]

Dale,

If I understand your objective correctly, the following should do the trick:

ActiveWindow.VisibleRange.Cells(1, 1).Activate

Let me know if this is what you had in mind.


Regards,
Mike

 

[DaleWatson123321]

Mike,

Thanks very much for your help.

So far, however, I've not been able to get the VisibleRange option to work.

I've tried adjusting the number of rows, but still no success. In selecting a named cell with VBA, Excel goes to the cell and places that selected cell in &quot;approximately&quot; the vertical center of the screen.

For example, if the named cell is A41 on Sheet2, the cursor will go to that cell, but the top cell showing on the screen is cell A32. I want the top cell showing to be cell A41.

Your &quot;ActiveWindow.VisibleRange.Cells(1, 1).Activate&quot; only takes the cursor to cell A32, and there is no change to what the user sees on the screen.

In adjusting the number of rows of the VisibleRange and/or adjusting the location of the cell to go to, there's still the following concerns:

1) Excel seems to be inconsistent in terms of going to the &quot;approximate&quot; center of the screen.

2) The size of a user's screen will vary depending on:
a) the size of monitor, and
b) the number of tool bars installed.

I've been VERY impressed with your VBA skills, so I'm hoping you can &quot;dig deeper into your VBA tool bag&quot; and pull out an alternative suggestion or solution.

Thanks Mike !!!

Regards, ...Dale

 

[rmikesmith]

Hi Dale,

Try this:

Sub MakeCellUpperLeft()
Dim Rng As Range

  Set Rng = Range("A41")
  With Rng
    .Select
    ActiveWindow.ScrollColumn = .Column
    ActiveWindow.ScrollRow = .Row
  End With
  
End Sub

Let me know if this meets the challenge

Regards,
Mike

 

[xlhelp]

Hi, Dale!

Is this what you need?

Sub topleft()

Dim TopLeftCell As Range

With ActiveWindow
Set TopLeftCell = Cells(.ScrollRow, .ScrollColumn)
End With

Application.Goto Reference:=TopLeftCell, Scroll:=True

End Sub

 

[DaleWatson123321]

Mike and Indu, thank you both for your help.

After testing both proposals, I found that Mike's works, and Indu's did not.

Indu, I found that your proposal ended up doing what Mike's FIRST proposal did. It goes to the the TopLeftCell of the current window - in this case cell A32. But I want the TopLeftCell of the current window to be A41. I don't want the user to view rows 32-40. I have named the destination cell (A41) as &quot;wkttls&quot;, and even after inserting a line to first select that named cell, your routine still goes to A32.

The following routine is what I ended up using. It uses Mike proposal, with a slight modification...

Sub Go_WeeklyTotals()
Worksheets(&quot;Totals&quot.Select
With Range(&quot;wkttls&quot
.Select
ActiveWindow.ScrollColumn = .Column
ActiveWindow.ScrollRow = .Row
End With
End Sub

Thanks again to both of you. In the spirit of awarding a STAR to the &quot;winnner&quot;, I have to award it to Mike.

I would think this type of solution should be of interest to MOST Excel users. I hope they take notice.

Thanks once again. ...Dale

 

[xlhelp]

in that case

Application.Goto Selection, True

will do what you need if A41 is already selected

 

[DaleWatson123321]

Indu,

Indeed, you're right, and you also earn a STAR. Thanks.

With your solution, here's the routine I used...

Sub Go_WeeklyTotals()
Worksheets(&quot;Totals&quot.Select
Range(&quot;wkttls&quot.Select
Application.Goto Selection, True
End Sub

Thanks again. ...Dale

 

[rmikesmith]

Way to go, Indu! A tidbit worth remembering.


Dale -- using Indu's insight here is an even more streamlined version of your sub:

Sub Go_WeeklyTotals()
    Worksheets("Totals").Select
    Application.Goto ActiveSheet.Range("wkttls"), True
End Sub

This worked w/o prior selection of the cell.

Regards,
Mike

 

[DaleWatson123321]

Mike,

Thanks for that refinement.

I just had to &quot;follow your lead&quot;. Here's the latest...

Sometimes, it's been possible to select a named range WITHOUT having to first select the SHEET.

In this particular case, however, it WAS necessary. However, by modifying yours and Indu's code, I found that the following is all that's required.

==================================
Sub Go_WeeklyTotals()
Application.Goto Range(&quot;wkttls&quot, True
End Sub
==================================

I hope others have been following this, as I expect they will find this VERY useful.

For the benefit of others... Using this method, the &quot;destination cell&quot; (in this case, a cell named &quot;wkttls&quot will ALWAYS be positioned in the top-left-corner of the screen.

And an added bonus... This even works when the destination cell is located somewhere else on the current screen - i.e. this will &quot;re-position&quot; the screen instead of just going to the destination cell on the same screen.

Regards, ...Dale

 

[onedtent]

It's quite amazing how far you can take refinement.

Thanks guys, a good learning experience for me.