Run Macro Only After Pressing Enter While in A4:A50

Modify_inc

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows
I currently have a macro that sorts my data and puts my cursor in the next empty cell for A4:A50, though it's manual requiring me to run the macro after each entry. How I can make my VBA code run the macro automatically after I press enter, but only when I press enter within the range of A4:A50? Is this even possible? I know I can do it for the entire sheet, but that would be overkill.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You could use a Worksheet Change event to do that.
What is the range of your data & how should it be sorted?
 
Upvote 0
I've tried this, but it seems to only accept one cell, not a range like A4:A50. Also, it runs the macro even if I just use the mouse to select the cell, which is not good. I can't even click a cell to edit it, because it sorts and moves the cursor to the empty cell, which is what I wanted, BUT only when I press enter, not when I use the mouse to select a cell for editing.

If Target.Address(False, False) = "A4:A50" Then Call SortAnswers

Is that because my Sub is the following?
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If so, I can't change that, my other code requires this. Is there anyway to run this new code under the same sheet though without altering my original Sub?
 
Upvote 0
You would need a change event not a selectionchange event.
 
Upvote 0
You would need a change event not a selectionchange event.

Ok, I got it to work with the enter key, and I can now select the cell with the mouse and make edits, but how do I add the range A4:A50 to that code, it only seems to work with one cell?

This does not work:
If Target.Address(False, False) = "A4:A50" Then Call SortAnswers

This does:
If Target.Address(False, False) = "A4" Then Call SortAnswers
 
Upvote 0
Can you post your entire code.
 
Upvote 0
Can you post your entire code.
It looks like a range is not allowed as stated below, but I did get it to work using If Target.Column = 1 Then Call.
I had to add Application.EnableEvents = False to prevent recursive looping.
Though I wish it would actually work for a specific range like A4:A50, the Target.Column = 1 option will work for now I guess.

The following use of Target.Address is NOT correct, and the code will not run:
If Target.Address = "$a$5" Then MsgBox "Success"
If Target.Address = "A1" Then MsgBox "Success"
If Target.Address = "$A$1:$A$10" Then MsgBox "Success"
If Target.Address = Range("$A$1:$A$10") Then MsgBox "Success"
 
Upvote 0
Yes it is, can you please post the code you are using.
I got it working!

I used the following:
If Not Application.Intersect(Target, Me.Range("A4:A50")) Is Nothing Then Call SortAnswers
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top