Trigger excel vba routine only when cursor leaves range?

Hi Experts,

I'm creating a spreadsheet that has a number of input ranges that the user will need to fill in.

For each of these ranges, I'd like to trigger a routine that runs only when the cursor moves outside that particluar range, rather than on every change within each range.

Not sure how I might accomplish that.

Thanks,
Deedub84
LVL 1
deedub84Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
When you say "cursor" do you mean the mouse cursor or the cell focus? I just want to be sure I understand exactly what you want.
0
deedub84Author Commented:
You're right, the cell focus.  Thanks for clarifying...
0
Saqib Husain, SyedEngineerCommented:
You might try something like this. This will not work the first time the worksheet is opened. Thereafter it will work. This particular routine checks for F12

Public ptarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ptarget Is Nothing Then
    Set ptarget = Target
Else
    If ptarget.Address = Range("F12").Address Then Call abc
    Set ptarget = Target
End If
End Sub

Sub abc()
'Your processing goes here
End Sub

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Anthony BerenguelCommented:
Here's a solution that might work for you. Try it out.
deedub84-Trigger-excel-vba-routi.zip
0
dlmilleCommented:
I think what the Asker wants is that if a user is inside a range, then moves outside that range with the cursor, a macro is initiated.  If the user keeps selecting ranges outside the "key" range, nothing else would happen, unless the user goes back INSIDE the key range, then OUTSIDE again.  Of course, if you only want to run the macro once, then add a boolean variable to check when the macro is run, then the code would prevent more than one instance from running.

This code will initiate the macro EACH time that happens:

code in the Sheet1's code page (assuming Sheet1 is where all the action takes place!):

Note:  Range("D6:M15") could be replaced with a reference to a range name, and could be multiple areas on the worksheet - re: multiple "Hot Spots".

Public pTarget As Range
Public bInRange As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If pTarget Is Nothing Then
        Set pTarget = Target
    ElseIf Not Intersect(Target, Range("D6:M15")) Is Nothing Then
        bInRange = True
    Else
        If bInRange Then
            bInRange = False
            Call myMacro
        End If
    End If
End Sub

Open in new window


Code in a public module (where your macro is that gets run):

Option Explicit

Sub myMacro()
    MsgBox "running macro because you moved out of the prescribed range!!!"
End Sub

Open in new window


If you only want to run the macro ONCE, no matter how many times the user goes INSIDE then OUTSIDE the range, replace the code in the public module with this:
Option Explicit
Public bRunOnce As Boolean
Sub myMacro()
    If Not bRunOnce Then
        bRunOnce = True
        MsgBox "running macro because you moved out of the prescribed range!!!"
    End If
End Sub

Open in new window


See attached demonstration workbook.

Is this what you're looking for?

Dave
runMacroMoveFromRange-r1.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
NFP:

You might also want:
Private Sub Worksheet_Activate()
    Set pTarget = ActiveCell
End Sub

Open in new window


and something in Workbook_Open to check if that sheet is active, and if so assign the active cell.
0
deedub84Author Commented:
Perfect thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.