Move selection right rather than down for just one cell?

[EssKayKay]

Is there a way to move the selection to the right (or left or up) rather than down for just one cell (I understand how to do this universally for all cells in the worksheet)?
As example, when G11 is selected and you strike Enter, the selection would go to H11 without going to G12.

The reason I don’t want G12 selected after G11 is when G12 is selected a message box appears. I don’t want to see the message after pressing Enter in G11 – only if G12 is selected by itself (not via Enter key after G11).

Thanks,
Steve

 

[Jaafar Tribak]

In the Worksheet Module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Application
        If Target.Address = Range("G11").Address Then
            .MoveAfterReturnDirection = xlToRight
        Else
            .MoveAfterReturnDirection = xlDown
        End If
    End With
End Sub

 

[EssKayKay]

In the Worksheet Module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Application
        If Target.Address = Range("G11").Address Then
            .MoveAfterReturnDirection = xlToRight
        Else
            .MoveAfterReturnDirection = xlDown
        End If
    End With
End Sub

Thank you very much Jaafar for your quick response. It's working perfectly.

Much appreciated,
Steve

 

[EssKayKay]

In the Worksheet Module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Application
        If Target.Address = Range("G11").Address Then
            .MoveAfterReturnDirection = xlToRight
        Else
            .MoveAfterReturnDirection = xlDown
        End If
    End With
End Sub

Hello Jaafar - I'm back,

I just noticed a slight problem with the Redirection routine. I have numerous other subroutines included in the worksheet. One of them simply copies/pastes a formula to a range. However, it bombs on the ActiveSheet.Paste line with a Run-time error ‘1004’ – Paste method of Worksheet class failed.

If I remove the Else command (.MoveAfterReturnDirection = xlDown) portion of the code (remark it out), the paste works fine. However, then the Enter button continues to move to the right.




Here is a simplified example of where one of the other subs is failing (see attached JPG’s):

Private Sub cmdTest1_Click()
     Range("P258").Select
     Selection.Copy
     Range("P33:P255").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
End Sub

Sorry,
Steve

 

[EssKayKay]

I learned a bit more. Like I said, when the

   Else
      .MoveAfterReturnDirection = xlDown

is enabled I cannot run all my subroutines, particularly Copy/Paste. If I disable (remove or remark out the .MoveAfterReturnDirection = xlDown everything works fine. Here's what I noticed - when the is Else > .MoveAfterReturnDirection = xlDown is enabled if I select Copy/Paste, the Paste option is greyed out. It does not allow me to paste anything.

Hope this helps shed some light on my issue.
I wish I could have edited the previous post but it times out way too soon so I had to add this post.

Thanks,
Steve K.

 

[EssKayKay]

Here's a Dropbox link to a sample worksheet (TryMe1.xlsb) showing what I described above.

Dropbox

www.dropbox.com

Again, wish I could have edited my preceding post and added this but I was too late.

 

[Jaafar Tribak]

Hi EssKayKay

I haven't downloaded your sample workbook but I think the following slight amendment should prevent the error:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Application
        If Target.Address = Range("G11").Address Then
            .MoveAfterReturnDirection = xlToRight
        Else
            If .MoveAfterReturnDirection <> xlDown Then
                .MoveAfterReturnDirection = xlDown
            End If
        End If
    End With
End Sub

 

[EssKayKay]

Ahh thank you so very much Jaafar. It appears to be working as expected. I'll keep playing with this but I think this is it.

Once again - much appreciated ,
Steve