Setting up display in Excel Macro

[syl93]

I am writing an Excel macro, and I want to select a certain cell on a certain worksheet and make it the very first cell displayed on that worksheet (uppermost and leftmost cell). I don't have a problem selecting a paticular cell on a worksheet, but I can't figure out how to make it the first cell displayed on the page.
I don't want to use scrolling because the number of times to scroll will be dependent on the size of the window.

 

[DaleWatson123321]

Hi syl93,

The following will work...

Sub Go_1()
Application.DisplayAlerts = False
Application.Goto Reference:="site1"
ActiveCell.Offset(20, 20).Select
Application.Goto Reference:="site1"
Application.DisplayAlerts = True
End Sub

Sub Go_2()
Application.DisplayAlerts = False
Application.Goto Reference:="site2"
ActiveCell.Offset(20, 20).Select
Application.Goto Reference:="site2"
Application.DisplayAlerts = True
End Sub

...where "site1" and "site2" and examples of Range Names.

You're probably aware of the method of assigning a Range Name, but for others who may be reading this, here are the steps:

a) Highlight the cell, or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

Caution: Don't use any names that Excel will conflict with cell coordinates. For example, don't use a name like &quot;E12&quot; - instead use &quot;E_12&quot; or &quot;_E12&quot;. Also, stay away from using names that can conflict with VBA commands - like Worksheets, Workbooks, Cells, etc. The safe methos, again, is to insert a &quot;_&quot; character - e.g. Worksheet_1.

I hope this helps. Please advise as to how you make out with it.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rmikesmith]

Syl93,

Here's another method:

Sub MakeCellUpperLeft()
Dim C As Range

  Set C = ThisWorkbook.Worksheets("Sheet1").Range("Z50")
  C.Select
  ActiveWindow.ScrollColumn = C.Column
  ActiveWindow.ScrollRow = C.Row

End Sub

Of course, you can assign your cell any way you want; the above is for demonstration. Good Luck!

M. Smith

 

[syl93]

You guys are great!
Both solutions worked perfectly.
Thanks!