hitting the Enter Key

[Zygoid]

in excel 2002, when i hit the enter key the cell wrap drops 1 cell down
Is there a way in VBA to control where the cell wrap goes when hittin
the enter key?

I would like it to go back to column(1), next row down.

also, if able, only in rows 13 thru 54

 

[Peo Sjoblom]

You can protect cells and only allow a certain movement but if you use the
tab when going right
and then press enter after the last cell of that row it will go back to the
start column where you
used tab

 

[Guest]

If you use Tab to move over to the right, when you want to go back to the first column you entered info in, then press Enter
Ex: If I start in B1 and Tab over four times, when I press enter I will move back to B2

You can also select all the cells you might use ahead of time, then you won't be able to use Tab or Enter to move outside of that range
Ex: If I select B13:C54, then when I press enter down to B54, when I press Enter again, I'll move to C13

You can easily select ranges for cells by typing in the Name Box like this: firstcell:last cell--ex: A1:E

t

----- Zygoid > wrote: ----

in excel 2002, when i hit the enter key the cell wrap drops 1 cell down
Is there a way in VBA to control where the cell wrap goes when hittin
the enter key

I would like it to go back to column(1), next row down.

also, if able, only in rows 13 thru 54

 

[Hank Scorpio]

in excel 2002, when i hit the enter key the cell wrap drops 1 cell down.
Is there a way in VBA to control where the cell wrap goes when hitting
the enter key?

I would like it to go back to column(1), next row down.

also, if able, only in rows 13 thru 54.

You may need to think this one through a little further. I imagine
that you want this to happen when the user hits [enter] after entering
a whole row of data. If (say) you have 5 columns of data to enter on
each row, then programming Excel to do what you describe above would
cause the following effect:
- User enters data in column A, presses [Enter]. The cell cursor drops
down 1 row to column A of the next row (which you've already indicated
doesn't work for you). User then moves the cursor back up to the
original row, second column.
- User enters a value into column B and presses [Enter]. Cursor drops
to column A of the next line. User moves the cursor back up.
- User enters a value into column C and presses [Enter]. Cursor drops
to column A of the next line. User moves the cursor back up.
- User enters a value into column D and presses [Enter]. Cursor drops
to column A of the next line. User throws chair through the monitor.

It might be helpful to describe exactly what the mode of data entry is
(what columns there are and so on) so that we can suggest the best
solution.

 

[David McRitchie]

You are referring to navigation not cell wrap.
The option controlling the Enter key can be found at
Tools (menu), Options (menu), Edit (tab),
settings: [x] Move selection after Enter {Down|Up|Right|Left}
Though I don't really think you need to change it, you can use
the arrow key.

But an Event Macro sounds more useful for your purpose.

Worksheet Events and Workbook Events
Worksheet_SelectionChange (#ws_sc)
Worksheet_SelectionChange to prevent entry past a column
http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc

Modified for your use: to not procede beyond column 8
and only be effect from rows 13 through 54

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.column < 8 then exit sub
IF Target.Row < 13 or Target.ZRow > 54 then exit sub
On error resume next 'MUST reenable events...
Application.EnableEvents = False
ActiveCell.Offset(1, 1 - Target.Column).Select
Application.EnableEvents = True
End Sub

Unlike regular macros which are installed in regular modules,
Worksheet Events are installed with the worksheet by right-clicking on
the sheettab, choose 'view code', and then paste in your macro.

BTW, Cell wrap is a format, cell, alignment option that
formats the cell to wrap text within a cell rather
than extending to display into the adjacent cells
unless they have a value, in which case the display
would be truncated.