Excel

Position Any Cell Top Left in Window

Ease of Use

Easy

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

The cell you require is positioned in the top left hand corner of the workbook window. 

Discussion:

It is often very desirable to position a certain cell in the top left hand corner of the window. A frequent example of this is having the cell A1 always showing in the top left hand corner when a workbook is opened. This goal can sometimes be achieved by using 'Select', however this is very unreliable simply because it's possible to select or activate cells that aren't in the visible portion of the window (Excel doesn't always assume they also need to be visible). You can write code that rectifies this situation, but there is already a simple inbuilt function that does this - the GoTo function, this is a one-line statement that replaces four (or more) lines of code that would normally be required to do the same thing using Select. The code below is an example that positions A1 top-left on every sheet when the workbook is opened (the zipped attachment includes some other examples). 

Code:

instructions for use 

			
Option Explicit 
 
Private Sub Workbook_Open() 
     
    Dim Sheet As Worksheet 
     
     '****************************
     'change A1 to suit your preference
    Const TopLeft As String = "A1" 
     '****************************
     
     '//loop thru all the sheets in the workbook
    Application.ScreenUpdating = False 
    For Each Sheet In Worksheets 
         
         'scroll:=True takes cell to the top-left of window
        Application.Goto Sheet.Range(TopLeft), scroll:=True 
         
    Next 
     
     '//open at sheet1
    Sheet1.Activate 
End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Tools/Project Explorer
  4. Select the ThisWorkbook module
  5. Copy and paste the code into the Module
  6. Now select File/Close and Return To Microsoft Excel
  7. Save your changes...
 

Test the code:

  1. Scroll and select any cell on all the worksheets
  2. Save and close the workbook
  3. Open the workbook and note that on every sheet A1 is now positioned in the top-left corner.
  4. Download the attachment and open to try some other examples
 

Sample File:

GoToExample.zip 13.92KB 

Approved by mdmackillop


This entry has been viewed 157 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express