What would be the VBA code to move to the next column after Enter but NOT after Tab?

[Michelle Lea]

I have the following code to run a macro that sorts column A after I hit the Enter key and keep the modified row selected. However, it also runs when I hit the Tab key. I don't want it to. Is there code that would run the macro when hitting Enter ONLY?

Thank you!


Private Sub Worksheet_Change(ByVal Target As Range)

'If Target.Column <> 8 Then Exit Sub

' Sort_After_Enter Macro

Dim lngR As Long

Dim strV As String

If Target.Cells.Count > 8 Then Exit Sub

strV = Target.Value

Application.EnableEvents = False

With Me.ListObjects("ContactsTable").Sort

.SortFields.Clear
.SortFields.Add2 Key:=Me.Range("ContactsTable[[#All],[COMPANY (Contact)]]"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply

End With

Target.EntireColumn.Find(strV, LookIn:=xlValues, LookAt:=xlWhole).Select

ActiveCell.Offset(0, 1).Activate

Application.EnableEvents = True

End Sub
-----
Sub ResetEvents()

Application.EnableEvents = True

End Sub

 

[Joe4]

Welcome to the Board!

That code will run automatically any time a cell is manually changed, regardless of how you move to the next cell.
If you only want the code to run at a certain time, maybe it would be better to put a button on the page that the user could click to run the code only when they want it to.
See here: Assign a macro to a Form or a Control button - Microsoft Support

 

[Michelle Lea]

Welcome to the Board!

That code will run automatically any time a cell is manually changed, regardless of how you move to the next cell.
If you only want the code to run at a certain time, maybe it would be better to put a button on the page that the user could click to run the code only when they want it to.
See here: Assign a macro to a Form or a Control button - Microsoft Support

I was trying to avoid doing anything manually, such as clicking the column header to sort or clicking a button, hence the auto-run of the Sort_After_Enter macro. Is there no way to code it so that it runs only after hitting the Enter key, not just whenever something is changed?

 

[Joe4]

I think you would have to use something like "OnKey", as shown in post 4 here: Run Macro if Enter is Pressed & in a certain row

I have never used that before. "Worksheet_Change" runs when data is manually entered, regardless of which key is used. Excel doesn't really care which key you used to enter the data.

 

[Michelle Lea]

I think you would have to use something like "OnKey", as shown in post 4 here: Run Macro if Enter is Pressed & in a certain row

I have never used that before. "Worksheet_Change" runs when data is manually entered, regardless of which key is used. Excel doesn't really care which key you used to enter the data.

Could I run an If-Then statement? Such as If tab key is pressed, Then don't run the macro, Else If enter key is pressed, run the macro?

 

[Joe4]

I am not sure. I never use "On Key".
Perhaps you can use the method shown in post 3 here. Just Change the "UP" references to "ENTER": Run Macro on Keypress