set tab or enter keys to automatically move to different c..

J9Y · External Since: Aug 29, 2005 Posts: 4

I'm working with an invoice document that someone created on Excel 2003.
It's a great document, but we want to program either the TAB key or the ENTER
key to move to a different cell within the worksheet. Example: After typing
the Date, enter automatically to Invoice Number, then enter automatically to
Company Name, and so on. Is there a way to start at cell N1 ,then program
the Enter key to automatically go to cell N7 after that, then C9 after that
and so on.

Any information is GREATLY appreciated!

Gord Dibben · External Since: Feb 23, 2004 Posts: 9178

J9Y

Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example.

Select the Second cell(B2) you want in the range then CRTL + click your way
through the range in the order you wish, ending with the First cell(A1). Name
this range under Insert>Name>Define>OK.

Now click on NameBox(top left corner above row 1 and col A), select the range
name to highlight the range. With these cells selected, you can input data
and Tab or Enter your way through the range in the order you selected.

Note: there is a limit of about 25 - 30 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

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

''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
'add more cells and Cases here as the above pattern shows.
End Select
End Sub

Gord Dibben Excel MVP

On Mon, 29 Aug 2005 09:51:07 -0700, J9Y <J9Y DeleteThis @discussions.microsoft.com> wrote:

>I'm working with an invoice document that someone created on Excel 2003.
>It's a great document, but we want to program either the TAB key or the ENTER
>key to move to a different cell within the worksheet. Example: After typing
>the Date, enter automatically to Invoice Number, then enter automatically to
>Company Name, and so on. Is there a way to start at cell N1 ,then program
>the Enter key to automatically go to cell N7 after that, then C9 after that
>and so on.
>
>Any information is GREATLY appreciated!
>

Mike · External Since: Aug 04, 2004 Posts: 560

If you have a new column that has the data in the order that you want them
entered such as column X.

Then highlight the first cell to receive data...hit = key.....highlight the
cell in X...hit enter. So the invoice problem...you probably don't want
column X to print out. You need to define a print area. Highlight what
should be printed out. Click on File...Print Area....Set Print Area.

"J9Y" wrote:

> I'm working with an invoice document that someone created on Excel 2003.
> It's a great document, but we want to program either the TAB key or the ENTER
> key to move to a different cell within the worksheet. Example: After typing
> the Date, enter automatically to Invoice Number, then enter automatically to
> Company Name, and so on. Is there a way to start at cell N1 ,then program
> the Enter key to automatically go to cell N7 after that, then C9 after that
> and so on.
>
> Any information is GREATLY appreciated!
>
>

Ian · External Since: Jul 05, 2005 Posts: 377

You won't be able to move in the order you quoted in your post, but you
could leave the required cells unlocked and tab between them. This method
works by rows, that is, it looks for the next available cell on the current
row and if it doesn't find any tries the next row and so on until it finds
an unlocked cell. If you save the file with N1 selected, it would tab to N7
then C9 and back to N1. Obviously there are more cells you need, but it
might be worth a look.

Select each of the cells you want to populate and set the cell protection to
unlocked (Format>Cells>Protection> uncheck Locked). Once this is done,
protect the worksheet (Tools>Protection>Protect Sheet). By using tab, you
will automatically jump to the next available cell, those available are the
ones you have unlocked.

--
Ian
--
"J9Y" <J9Y.DeleteThis@discussions.microsoft.com> wrote in message
news:8445FC32-6007-4F01-BBC7-F2E0681918BA@microsoft.com...
> I'm working with an invoice document that someone created on Excel 2003.
> It's a great document, but we want to program either the TAB key or the
> ENTER
> key to move to a different cell within the worksheet. Example: After
> typing
> the Date, enter automatically to Invoice Number, then enter automatically
> to
> Company Name, and so on. Is there a way to start at cell N1 ,then program
> the Enter key to automatically go to cell N7 after that, then C9 after
> that
> and so on.
>
> Any information is GREATLY appreciated!
>
>

J9Y · External Since: Aug 29, 2005 Posts: 4

Thanks for your reply - I really appreciated your response.

"Ian" wrote:

> You won't be able to move in the order you quoted in your post, but you
> could leave the required cells unlocked and tab between them. This method
> works by rows, that is, it looks for the next available cell on the current
> row and if it doesn't find any tries the next row and so on until it finds
> an unlocked cell. If you save the file with N1 selected, it would tab to N7
> then C9 and back to N1. Obviously there are more cells you need, but it
> might be worth a look.
>
> Select each of the cells you want to populate and set the cell protection to
> unlocked (Format>Cells>Protection> uncheck Locked). Once this is done,
> protect the worksheet (Tools>Protection>Protect Sheet). By using tab, you
> will automatically jump to the next available cell, those available are the
> ones you have unlocked.
>
> --
> Ian
> --
> "J9Y" <J9Y DeleteThis @discussions.microsoft.com> wrote in message
> news:8445FC32-6007-4F01-BBC7-F2E0681918BA@microsoft.com...
> > I'm working with an invoice document that someone created on Excel 2003.
> > It's a great document, but we want to program either the TAB key or the
> > ENTER
> > key to move to a different cell within the worksheet. Example: After
> > typing
> > the Date, enter automatically to Invoice Number, then enter automatically
> > to
> > Company Name, and so on. Is there a way to start at cell N1 ,then program
> > the Enter key to automatically go to cell N7 after that, then C9 after
> > that
> > and so on.
> >
> > Any information is GREATLY appreciated!
> >
> >
>
>
>

J9Y · External Since: Aug 29, 2005 Posts: 4

Thanks for the advice - it worked great and saved me alot of time!

"Gord Dibben" wrote:

> J9Y
>
> Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example.
>
> Select the Second cell(B2) you want in the range then CRTL + click your way
> through the range in the order you wish, ending with the First cell(A1). Name
> this range under Insert>Name>Define>OK.
>
> Now click on NameBox(top left corner above row 1 and col A), select the range
> name to highlight the range. With these cells selected, you can input data
> and Tab or Enter your way through the range in the order you selected.
>
> Note: there is a limit of about 25 - 30 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
>
> There is a third method which requires VBA and a Worksheet_Change event.
>
> ''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
> 'add more cells and Cases here as the above pattern shows.
> End Select
> End Sub
>
>
> Gord Dibben Excel MVP
>
>
> On Mon, 29 Aug 2005 09:51:07 -0700, J9Y <J9Y.TakeThisOut@discussions.microsoft.com> wrote:
>
> >I'm working with an invoice document that someone created on Excel 2003.
> >It's a great document, but we want to program either the TAB key or the ENTER
> >key to move to a different cell within the worksheet. Example: After typing
> >the Date, enter automatically to Invoice Number, then enter automatically to
> >Company Name, and so on. Is there a way to start at cell N1 ,then program
> >the Enter key to automatically go to cell N7 after that, then C9 after that
> >and so on.
> >
> >Any information is GREATLY appreciated!
> >
>
>

J9Y · External Since: Aug 29, 2005 Posts: 4

Thanks for the reply - I really appreciate your response!

"Mike" wrote:

> If you have a new column that has the data in the order that you want them
> entered such as column X.
>
> Then highlight the first cell to receive data...hit = key.....highlight the
> cell in X...hit enter. So the invoice problem...you probably don't want
> column X to print out. You need to define a print area. Highlight what
> should be printed out. Click on File...Print Area....Set Print Area.
>
> "J9Y" wrote:
>
> > I'm working with an invoice document that someone created on Excel 2003.
> > It's a great document, but we want to program either the TAB key or the ENTER
> > key to move to a different cell within the worksheet. Example: After typing
> > the Date, enter automatically to Invoice Number, then enter automatically to
> > Company Name, and so on. Is there a way to start at cell N1 ,then program
> > the Enter key to automatically go to cell N7 after that, then C9 after that
> > and so on.
> >
> > Any information is GREATLY appreciated!
> >
> >

Gayla · External Since: May 12, 2006 Posts: 28

I was attempting to follow your instructions for tab order and when I choose
Insert-Name-Define. Define is not able to be chosen. I have 2003 version.
Any suggestions?

"Gord Dibben" wrote:

> J9Y
>
> Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example.
>
> Select the Second cell(B2) you want in the range then CRTL + click your way
> through the range in the order you wish, ending with the First cell(A1). Name
> this range under Insert>Name>Define>OK.
>
> Now click on NameBox(top left corner above row 1 and col A), select the range
> name to highlight the range. With these cells selected, you can input data
> and Tab or Enter your way through the range in the order you selected.
>
> Note: there is a limit of about 25 - 30 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
>
> There is a third method which requires VBA and a Worksheet_Change event.
>
> ''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
> 'add more cells and Cases here as the above pattern shows.
> End Select
> End Sub
>
>
> Gord Dibben Excel MVP
>
>
> On Mon, 29 Aug 2005 09:51:07 -0700, J9Y <J9Y DeleteThis @discussions.microsoft.com> wrote:
>
> >I'm working with an invoice document that someone created on Excel 2003.
> >It's a great document, but we want to program either the TAB key or the ENTER
> >key to move to a different cell within the worksheet. Example: After typing
> >the Date, enter automatically to Invoice Number, then enter automatically to
> >Company Name, and so on. Is there a way to start at cell N1 ,then program
> >the Enter key to automatically go to cell N7 after that, then C9 after that
> >and so on.
> >
> >Any information is GREATLY appreciated!
> >
>
>

Gord Dibben · External Since: Feb 23, 2004 Posts: 9178

Perhaps your worksheet is protected.

Look under Tools>Protection to see if Unprotect Sheet is available.

You may have to supply a password.

Gord

On Tue, 7 Nov 2006 16:24:02 -0800, Gayla <Gayla.TakeThisOut@discussions.microsoft.com>
wrote:

>I was attempting to follow your instructions for tab order and when I choose
>Insert-Name-Define. Define is not able to be chosen. I have 2003 version.
>Any suggestions?
>
>"Gord Dibben" wrote:
>
>> J9Y
>>
>> Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example.
>>
>> Select the Second cell(B2) you want in the range then CRTL + click your way
>> through the range in the order you wish, ending with the First cell(A1). Name
>> this range under Insert>Name>Define>OK.
>>
>> Now click on NameBox(top left corner above row 1 and col A), select the range
>> name to highlight the range. With these cells selected, you can input data
>> and Tab or Enter your way through the range in the order you selected.
>>
>> Note: there is a limit of about 25 - 30 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
>>
>> There is a third method which requires VBA and a Worksheet_Change event.
>>
>> ''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
>> 'add more cells and Cases here as the above pattern shows.
>> End Select
>> End Sub
>>
>>
>> Gord Dibben Excel MVP
>>
>>
>> On Mon, 29 Aug 2005 09:51:07 -0700, J9Y <J9Y.TakeThisOut@discussions.microsoft.com> wrote:
>>
>> >I'm working with an invoice document that someone created on Excel 2003.
>> >It's a great document, but we want to program either the TAB key or the ENTER
>> >key to move to a different cell within the worksheet. Example: After typing
>> >the Date, enter automatically to Invoice Number, then enter automatically to
>> >Company Name, and so on. Is there a way to start at cell N1 ,then program
>> >the Enter key to automatically go to cell N7 after that, then C9 after that
>> >and so on.
>> >
>> >Any information is GREATLY appreciated!
>> >
>>
>>

MikeConnell · Joined: May 09, 2007 Posts: 1

Ian,

I am having a particular kind of difficulty - I have used that feature for many moons (Protecting a sheet with unlocked cells and tabbing between them), however, every now and then, that feature just stops working.

When I press the TAB button on a protected sheet with unlocked cells, I am used to moving to the next unlocked open cell - it stopped working in one of my files.

When I press the tab key while on a locked cell, the cursor doesn't move.
When I press the tab key while on an unlocked cell, it moves to the unlocked cells next to it and so on, but it won't go to the next line of unlocked cells.
Lastly, when I position in a row of unlocked cells and tab across more unlocked cells, when I get to the end of the unlocked cells, my tab position goes UP into the first line of unlocked cells after my locked cells - strange happenings.

Thanx.