enter 4 colums of data in a row then shift after hitting enter

**tintin007** · 06-01-2014, 03:03 PM

Hi,
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row. Whats the best way to do this ?? Any help would be appreciated. post corrected i think. cheers

cheers Mark



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If [E2] <> "" Then
[E2].Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[E2].Select
End If

Application.EnableEvents = True

If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If [D2] <> "" Then
[D2].Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[D2].Select
End If

Application.EnableEvents = True

If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If [C2] <> "" Then
[C2].Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[C2].Select
End If

Application.EnableEvents = True

If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If [B2] <> "" Then
[B2].Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[B2].Select
End If
Application.EnableEvents = True

If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If [A2] <> "" Then
[A2].Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A2].Select
End If
Application.EnableEvents = True
End Sub

**HaHoBe** · 06-01-2014, 03:12 PM

Hi, Mark,

you could have edited the original post instead.

all code goes behind the sheet. This will change the cursor movement to go right after each Enter when you activate the sheet and return to any other movement if you deactivate the sheet. The code will only get triggered once you reach Column E and have all cells in range A2:D2 filled:

Option Explicit
Dim lngCursor As Long

Private Sub Worksheet_Activate()
With Application
  lngCursor = .MoveAfterReturnDirection
  .MoveAfterReturnDirection = xlToRight
End With
End Sub

Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection = lngCursor
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 And Target.Row = 2 Then
  If WorksheetFunction.CountBlank(Range("A2:D2")) = 0 Then
    Application.EnableEvents = False
    Range("A2:D2").Insert shift:=xlShiftDown
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 4).Copy Range("A2")
    Application.CutCopyMode = False
    Range("A2").Select
    Application.EnableEvents = True
  End If
End If
End Sub

There may only be one event with the name of Worksheet_SelectionChange behind a sheet so you would to delete or comment any other code out in order to make the code run.

If there is only one sheet in the workbook you would need to use different events in ThisWorkbook in order to get the cursor movement working as expected.

Ciao,
Holger