Cell tab order

[Craig]

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

 

[Tom Ogilvy]

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.

 

[~Craig]

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, Define>OK 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

 

[Tom Ogilvy]

I selected my second cell in Area1, then used ctrl to select the remainder
in order

I named this AAAA

I did area 2 selecting the first cell and then the remainder in order

I named this BBBB

I did area 3 the same as two, but ended with the first cell in Area 1

I named this CCCC

I then went into Insert=>Name =>Define

name: DDDD
Refers to: =AAAA,BBBB,CCCC

then clicked ADD

Now when I select DDDD, I can tab through all three areas in order of
selection

--
Regards,
Tom Ogilvy

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, Define>OK 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





.

 

[Guest]

Thank you Tom.

I am using Excel 2000 and tried several times to combine
the three ranges into one with no luck. It appears to work
but the combine range that I named doesn't appear in the
dropdown where ranges 1, 2, and 3 appear.

Craig

-----Original Message-----
I selected my second cell in Area1, then used ctrl to select the remainder
in order

I named this AAAA

I did area 2 selecting the first cell and then the remainder in order

I named this BBBB

I did area 3 the same as two, but ended with the first cell in Area 1

I named this CCCC

I then went into Insert=>Name =>Define

name: DDDD
Refers to: =AAAA,BBBB,CCCC

then clicked ADD

Now when I select DDDD, I can tab through all three areas in order of
selection

--
Regards,
Tom Ogilvy

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, Define>OK 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


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


.

.

 

[Tom Ogilvy]

Computed ranges don't appear in the the name box (because they may not be
ranges I assume). You can type it in however. Same with F5 (Go to)

--
Regards,
Tom Ogilvy

Thank you Tom.

I am using Excel 2000 and tried several times to combine
the three ranges into one with no luck. It appears to work
but the combine range that I named doesn't appear in the
dropdown where ranges 1, 2, and 3 appear.

Craig

-----Original Message-----
I selected my second cell in Area1, then used ctrl to select the remainder
in order

I named this AAAA

I did area 2 selecting the first cell and then the remainder in order

I named this BBBB

I did area 3 the same as two, but ended with the first cell in Area 1

I named this CCCC

I then went into Insert=>Name =>Define

name: DDDD
Refers to: =AAAA,BBBB,CCCC

then clicked ADD

Now when I select DDDD, I can tab through all three areas in order of
selection

--
Regards,
Tom Ogilvy

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, Define>OK 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


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


.

.

 

[Gord Dibben]

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

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, Define>OK 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





.

 

[Guest]

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

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, Define>OK 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


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


.

.

 

[~Craig]

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.....

$

D$6,$F$6,$H$6,

$

10,$B$12,$D$12,

$

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

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, Define>OK 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


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


.

.

.