Use the name box to the left of the formula bar to get to a specific cellType 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 cellsType A30000:B30001 into the Name Box.
To get to the last used cellTo get to the last used cell (intersection of the last used row and the last used column) -- Ctrl+End
To return to Cell A1To get to the first cell of the worksheet use Ctrl+Home
Type A65536 into the Name Box then End then UpArrow
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:
- End then DnArrow
- double click on the bottom border of a cell
See some macros on my Toolbars page.
generic macro personal.xls!GotoTopOfCurrentColumn
generic macro personal.xls!GotoBottomofCurrentColumn
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 operations are poorly documented and poorly indexed in HELP, for more mouse operations see Mouse Operations on my Fill-Handle page.
"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.
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 SheetYou 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.
Note: The TAB key will take you to A1 if you attempt to go beyond the used range.
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
- Unprotected cells among other cells with protection and locked.
- 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.
- Event macro to guide you after change Event or after a selection Change event.
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 SubYou 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.
Shortcut keys Ctrl+PageUp and Ctrl+PageDN
to go sequentially backward or forward through the tabs.
A programming equivalent with a pair of macros and their toolbar buttons
that I find easier to use because I can just use the mouse.
Navigating to Previous, Next, or specific sheet (#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)
Toolbar Button Assignment I created for use in navigating in XL2000
Hyperlinks, Back (Alt+LeftArrow) and Forward (Alt+RightArrow) buttons:
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
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
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
personal.xls!MacroDialogBox (on Toolbars Page)
GoToSub (on BuildTOC page)
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