Tab Through excel spreadsheet

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

Does anyone know if there is a way to set up tabs that
would allow you to tab only to the cells that you want to
enter data without having to go through every cell?
 
Try this on a example workbook

Uncheck the locked property of all cells you want to use first
Select the cells
Ctrl-1
On the Protection tab uncheck locked
Paste this event in the thisworkbook module
and save the file and close it.

When you open the file you only can select unlocked cells
in each sheet.

Private Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Worksheets
Sh.Select
Sh.Protect userinterfaceonly:=True
Sh.EnableSelection = xlUnlockedCells
Next
Sheets(1).Select
Application.ScreenUpdating = True
End Sub
 
Another way without code is this

Uncheck the locked property of all cells you want to use first
Select the cells
Ctrl-1
On the Protection tab uncheck locked
Protect the sheet

If you use the Tab key now you only select unprotected cells
 
Hi Terry,

Another way is to name the cells, then click on the name box arrow and click
the named range. It will be selected and you enter data and hit TAB or
ENTER. Moves you right through the cells.

To set it up, click on the second cell in the sequence and hold down Ctrl
and click on each other cell in the order you want and end on the first
cell. Now, with the cells still selected click in the name box and name the
range.

Using named ranges will only take about 25 +/- cells (I forget for sure) so
it may be necessary to have several named ranges to accommodate your sheet.
But this is not bad in that you can use names that are pertinent to the info
that is being input.

HTH
Regards,
Howard
 
Howard/Terry

Info only......

Note: there is a limit of about 25 cells to a range using this method due
to a 255 character limit in a named range. Longer sheet names will reduce the
number of cells considerably.

If more needed, you can enter them manually in the "refers to" box.

From Debra Dalgleish.....
The limit is 255 characters in the Name definition. For example, I can
define a range of 46 non-contiguous cells, with the following string:

=$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$4,$B$6,$D$6,$F$6,$H$6,
$J$6,$B$8,$D$8,$F$8,$H$8,$J$8,$B$10,$D$10,$F$10,$H$10,$J$10,$B$12,$D$12,
$F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14,$J$14,$B$16,$D$16,$F$16,$H$16,
$J$16,$B$18,$D$18,$F$18,$H$18,$J$18,$L$3
Click to expand...

Gord Dibben XL2002
 
Hi Gord,

Good info. I knew there were some restrictions using this method but never
knew why.
Thanks for the explanation.

Regards,
Howard
 
Back
Top