Cell tab order
 

Craig

Perhaps you have run into the 255 char limit.

My notes from a previous post on naming a range......

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. A longer sheet name 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

No need to enter the $ signs while entering in the refersto: box.

When finished entering the cells, highlight them in the the refers to: box and
hit F4 to add the $ signs to all at a whack.

There is a third method which requires VBA and a Worksheet_Change event.

If you want to go to the trouble to add the cells to the code below, this
method will work.

''moves from C2 through E5 at entry
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$2"
Range("C5").Select
Case "$C$5"
Range("E2").Select
Case "$E$2"
Range("E5").Select
'and on and on
End Select
End Sub

Gord Dibben Excel MVP

On Wed, 31 Mar 2004 07:27:16 -0800, "~Craig"
wrote:

Thank you Tom.

The macro performed as you stated - controlling the
direction the cursor moves when return is entered.

I would like to hit tab to jump to 25 specific cells in
this order: A1, B14, J5, H24, A15 etc. (I know - all over
the place) I did select the second cell I wanted in the
range then CRTL + click my way through the range, ending
with the first cell in the range. Then I tried naming the
range under insert, Name, DefineOK but I received the
error message: [Your formula contains an invalid external
reference to a worksheet. Verify that the path, workbook,
and range or cell reference are correct and try again.]

I am not referencing an external worksheet or workbook. I
am trying to do this all on one tab but I think I must of
bumped into the 255 character limit.

My workaround was to divide the spreadsheet into three
sections and that is working but ideally it would be best
if I could navigate the entire spreadsheet with one range
or in another matter.

Best regards,

Craig

-----Original Message-----
If you run the code, it will change settings in
tools=options=Edit tab
for the option move selection after enter. Basically it
stores the current
setting, then makes the change to move down after a
return.

this runs when the sheet is activated if it is placed in
the sheet code
module. It resets the original settings when the user
activates a different
sheet.

I don't believe this affects the behavior of the tab key,
however. Only the
enter key.

If that is what you want, then you are set. If not, post
back with
specifics on what you are trying to do and perhaps
someone can provide a
method to help.

--
Regards,
Tom Ogilvy


"Craig" wrote in
message
.. .
I would like to control the order I tab within my
spreadsheet and found the code below but don't know how
to
properly use it.


Option Explicit
Public aut As String
Public curmove, ckwkly As Boolean
Public curdirec As Long

Private Sub Worksheet_Activate()
curmove = Application.MoveAfterReturn
curdirec = Application.MoveAfterReturnDirection
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
aut = Application.EnableAutoComplete
Application.EnableAutoComplete = False
End Sub


Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection = curdirec
Application.MoveAfterReturn = curmove
If aut < Empty Then
Application.EnableAutoComplete = aut
End Sub


.

Cell tab order
 

Thank you Gord!

This is working except the label I gave the range doesn't
appear in the Name Box drop down menu. If I type the range
name in then it works. Is there a way for the named range
to appear as a selection?

Craig

-----Original Message-----
Craig

Perhaps you have run into the 255 char limit.

My notes from a previous post on naming a range......

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. A longer
sheet name 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

No need to enter the $ signs while entering in the
refersto: box.

When finished entering the cells, highlight them in the
the refers to: box and
hit F4 to add the $ signs to all at a whack.

There is a third method which requires VBA and a
Worksheet_Change event.

If you want to go to the trouble to add the cells to the
code below, this
method will work.

''moves from C2 through E5 at entry
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$2"
Range("C5").Select
Case "$C$5"
Range("E2").Select
Case "$E$2"
Range("E5").Select
'and on and on
End Select
End Sub

Gord Dibben Excel MVP

On Wed, 31 Mar 2004 07:27:16 -0800, "~Craig"
wrote:

Thank you Tom.

The macro performed as you stated - controlling the
direction the cursor moves when return is entered.

I would like to hit tab to jump to 25 specific cells in
this order: A1, B14, J5, H24, A15 etc. (I know - all
over
the place) I did select the second cell I wanted in the
range then CRTL + click my way through the range, ending
with the first cell in the range. Then I tried naming
the
range under insert, Name, DefineOK but I received the
error message: [Your formula contains an invalid
external
reference to a worksheet. Verify that the path,
workbook,
and range or cell reference are correct and try again.]

I am not referencing an external worksheet or workbook.
I
am trying to do this all on one tab but I think I must
of
bumped into the 255 character limit.

My workaround was to divide the spreadsheet into three
sections and that is working but ideally it would be
best
if I could navigate the entire spreadsheet with one
range
or in another matter.

Best regards,

Craig

-----Original Message-----
If you run the code, it will change settings in
tools=options=Edit tab
for the option move selection after enter. Basically it
stores the current
setting, then makes the change to move down after a
return.

this runs when the sheet is activated if it is placed
in
the sheet code
module. It resets the original settings when the user
activates a different
sheet.

I don't believe this affects the behavior of the tab
key,
however. Only the
enter key.

If that is what you want, then you are set. If not,
post
back with
specifics on what you are trying to do and perhaps
someone can provide a
method to help.

--
Regards,
Tom Ogilvy


"Craig" wrote in
message
. ..
I would like to control the order I tab within my
spreadsheet and found the code below but don't know
how
to
properly use it.


Option Explicit
Public aut As String
Public curmove, ckwkly As Boolean
Public curdirec As Long

Private Sub Worksheet_Activate()
curmove = Application.MoveAfterReturn
curdirec = Application.MoveAfterReturnDirection
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
aut = Application.EnableAutoComplete
Application.EnableAutoComplete = False
End Sub


Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection =
curdirec
Application.MoveAfterReturn = curmove
If aut < Empty Then
Application.EnableAutoComplete = aut
End Sub


.


.

Cell tab order
 

Thank you all!

I shortened the tab name to "A" and then I was able to
click in all the cells necessary to complete this project.

Though I don't understand why when I manually define a
region (i.e., without clicking in the cells) the name does
not appear in the Name Box drop down.

Craig


-----Original Message-----
Thank you Gord!

This is working except the label I gave the range doesn't
appear in the Name Box drop down menu. If I type the
range
name in then it works. Is there a way for the named range
to appear as a selection?

Craig

-----Original Message-----
Craig

Perhaps you have run into the 255 char limit.

My notes from a previous post on naming a range......

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. A longer
sheet name 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

No need to enter the $ signs while entering in the
refersto: box.

When finished entering the cells, highlight them in the
the refers to: box and
hit F4 to add the $ signs to all at a whack.

There is a third method which requires VBA and a
Worksheet_Change event.

If you want to go to the trouble to add the cells to the
code below, this
method will work.

''moves from C2 through E5 at entry
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$2"
Range("C5").Select
Case "$C$5"
Range("E2").Select
Case "$E$2"
Range("E5").Select
'and on and on
End Select
End Sub

Gord Dibben Excel MVP

On Wed, 31 Mar 2004 07:27:16 -0800, "~Craig"
wrote:

Thank you Tom.

The macro performed as you stated - controlling the
direction the cursor moves when return is entered.

I would like to hit tab to jump to 25 specific cells in
this order: A1, B14, J5, H24, A15 etc. (I know - all
over
the place) I did select the second cell I wanted in the
range then CRTL + click my way through the range,
ending
with the first cell in the range. Then I tried naming
the
range under insert, Name, DefineOK but I received the
error message: [Your formula contains an invalid
external
reference to a worksheet. Verify that the path,
workbook,
and range or cell reference are correct and try again.]

I am not referencing an external worksheet or workbook.
I
am trying to do this all on one tab but I think I must
of
bumped into the 255 character limit.

My workaround was to divide the spreadsheet into three
sections and that is working but ideally it would be
best
if I could navigate the entire spreadsheet with one
range
or in another matter.

Best regards,

Craig

-----Original Message-----
If you run the code, it will change settings in
tools=options=Edit tab
for the option move selection after enter. Basically
it
stores the current
setting, then makes the change to move down after a
return.

this runs when the sheet is activated if it is placed
in
the sheet code
module. It resets the original settings when the user
activates a different
sheet.

I don't believe this affects the behavior of the tab
key,
however. Only the
enter key.

If that is what you want, then you are set. If not,
post
back with
specifics on what you are trying to do and perhaps
someone can provide a
method to help.

--
Regards,
Tom Ogilvy


"Craig" wrote in
message
.. .
I would like to control the order I tab within my
spreadsheet and found the code below but don't know
how
to
properly use it.


Option Explicit
Public aut As String
Public curmove, ckwkly As Boolean
Public curdirec As Long

Private Sub Worksheet_Activate()
curmove = Application.MoveAfterReturn
curdirec = Application.MoveAfterReturnDirection
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
aut = Application.EnableAutoComplete
Application.EnableAutoComplete = False
End Sub


Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection =
curdirec
Application.MoveAfterReturn = curmove
If aut < Empty Then
Application.EnableAutoComplete = aut
End Sub


.


.

.