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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,311
Office Version
  1. 365
Platform
  1. Windows
You could use a Worksheet Change event to do that.
What is the range of your data & how should it be sorted?
 

Modify_inc

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,311
Office Version
  1. 365
Platform
  1. Windows
You would need a change event not a selectionchange event.
 

Modify_inc

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,311
Office Version
  1. 365
Platform
  1. Windows
Can you post your entire code.
 

Modify_inc

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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"
 

Modify_inc

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,311
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

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
Top