Navigating within a Sheet and Around a Workbook

Location: http://www.mvps.org/dmcritchie/excel/navigation.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
 
Navigating: within a Sheet, to Another Sheet, to Code in the Visual Basic Editor

Navigating within a Sheet (#withinsheet)

Quick Navigation around the sheet

Use the name box to the left of the formula bar to get to a specific cell

Type A30000 into the namebox.

alternate: Press F5 - type A30000 in Reference - press Enter.

Use the name box to the to identify a specific range of cells

Type  A30000:B30001  into the Name Box.

To get to the last used cell

To get to the last used cell (intersection of the last used row and the last used column) -- Ctrl+End

To return to Cell A1

To get to the first cell of the worksheet use Ctrl+Home

To get to the last entry in Column A

Type A65536 into the Name Box then End then UpArrow

If there are no intervening blanks in Column A

If there are no intervening blanks in Column A you can get to the last used cell in the column using one of the following from a cell in Column A:

Some macros for quick naviation

See some macros on my Toolbars page.
generic macro  personal.xls!GotoTopOfCurrentColumn
generic macro  personal.xls!GotoBottomofCurrentColumn

Mouse Mouse Clicking for Navigation

double-clicking the border of a cell, you will see a the cursor change from a white cross to a pointer.
Double-click on a border in the direction you want to move, same as End+ArrowKey

You will find more information in Excel HELP (F1) --> index --> move --> move or copy cells

Mouse Mouse operations are poorly documented and poorly indexed in HELP, for more mouse operations see Mouse Operations on my Fill-Handle page.

Navigation to specific cells as in a Worksheet

"Down" is the normal direction that one goes after hitting Enter.  You can change this with Tools, Options, Edit, Move Cell after Enter: (Up/right/down/left).

To restrict entry to Column A or to other specific cells you can use Cell Protection. 

Restricting Navigation by using Protected Cells

Cells are protected by default, but not effective until you protect the sheet.  So you just have to unlock the cells in column A then protect the sheet.
Select Column A Format, Cells, Protection, uncheck Locked (both unchecked) Tools, Protect Sheet

Note: The TAB key will take you to A1 if you attempt to go beyond the used range.

You can unprotect any cell that you want to help with navigation.  You might want to color code cells that can or cannot be entered into.  To help you identify such cells you can use Edit, GoTo, [Special], and choose something like formulas, or blank cells, depending on which you want to color.

Navigation with the TAB key will be across the row, skipping any protected cell, and when it can't go further on a row it skips down to the next row and looks for a protected cell (repeat as necssary).

Navigation can be provided by the following or combinations of 
  1. Unprotected cells among other cells with protection and locked.
  2. Selection:  selections need not be contiguous or in order
      B20, C20, E20, F20, H20, N27, O27, P27, m34, B21
    Problem is if you break the selection by not tabbing to the next cell in selection.
  3. Event macro to guide you after change Event or after a selection Change event.

Restricting Navigation to Columns A, B, C using an Event Macro

Another way to restrict Navigation is with an Event Macro.
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Target.Column > 3 Then
        ActiveCell.Offset(1, 1 - Target.Column).Select
     End If
  End Sub
You could set a cell to be checked within the macro to ignore the jump to next row so you could do some maintenance.

You would use the RtArrow key though you could change the direction of the Enter key from Down to Right.  Use of the TAB key as mentioned before would take you to A1 if you attempt to go outside the used range.


Navigation to Another Sheet (#anothersheet)

Some VBA Code
  Sheets("My Sheet").Select
  Sheets("My Other Sheet").Activate Some additional code snippets in Some Sheet related coding (#sheetcoding)

Shortcut keys     Ctrl+PageUp and Ctrl+PageDN
  to go sequentially backward or forward through the tabs.
    http://www.mvps.org/dmcritchie/excel/shortx2k.htm

A programming equivalent with a pair of macros and their toolbar buttons Previous Sheet -- Ctrl+PageUP  Next Sheet -- Ctrl+PageDN 
    that I find easier to use because I can just use the mouse. 
  Navigating to Previous, Next, or specific sheet (#navigate)
    http://www.mvps.org/dmcritchie/excel/buildtoc.htm#navigate

You can right click on the sheet tab navigation arrow for a menu of worksheet tabs. 
The programming equivalent of bringing up the "More Sheets" (thanks to Chip Pearson) is
  Navigation to a Sheetname (#MoreSheets)
    http://www.mvps.org/dmcritchie/excel/dialog.htm

Toolbar Button Assignment I created for use in navigating in XL2000
       Insert Row, Formulas  Top of Column  Bottom of Column  Insert Footer  #1 Reassign for testing  #2 Reassign for testing  #3 Reassign for testing  #4 Reassign for testing  List Subs and Functions  (sour face) Reassign for
testing  Macros [Alt+F8]  GoTo Sub or Function  Backup By Date  xl2html, convert selection to HTML  Previous Sheet -- Ctrl+PageUP  Next Sheet -- Ctrl+PageDN  MakeHTML_Link  Euro  IE5 - Goto HTML  CharMap  notepad  ClearConstants  DeleteThisSheet - Delete Sheet
    http://www.mvps.org/dmcritchie/excel/toolbars.htm#navigating

Hyperlinks, Back (Alt+LeftArrow) and Forward (Alt+RightArrow) buttons:  Back  Back 
If you used a hyperlink to switch to another sheet or to another cell you can use your web browser's BACK and FORWARD buttons which you can install on your toolbar with Tools, Customize, Commands (tab), Category: Web ...
More information on Hyperlinks in
  http://www.mvps.org/dmcritchie/buildtoc.htm   (throughout)
  http://www.mvps.org/dmcritchie/sheets.htm#hyperlink
  http://www.mvps.org/dmcritchie/buildtoc.htm

Along the lines of having a hyperlink would be an event macro to double click on a cell with the sheetname and have the macro take you to the sheet -- without using a hyperlink on the sheet.  See GoToSheet macro in
  http://www.mvps.org/dmcritchie/excel/code/gotostuff.txt

Something similar as an Event macro that you double click on a cell that shows the sheetname. 
The Event macro activates from the sheet it is installed in. http://www.mvps.org/dmcritchie/excel/event.htm
You will not get the Back and Forward advantage of a hyperlink.

 Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
   Cancel = True   'Get out of edit mode 
   Worksheets(Trim(Target.Value)).Activate
 End Sub

Navigating to Code in the Visual Basic Editor (#VBE)

Macros [Alt+F8]  personal.xls!MacroDialogBox (on Toolbars Page)
GoToSub GoToSub (on BuildTOC page)


You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on November 12, 2001.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2006,  F. David McRitchie,  All Rights Reserved