Solved

Trigger excel vba routine only when cursor leaves range?

Posted on 2012-04-11
7
313 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:deedub84
7 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 37833655
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
 
LVL 1

Author Comment

by:deedub84
ID: 37833704
You're right, the cell focus.  Thanks for clarifying...
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37833809
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 37833834
Here's a solution that might work for you. Try it out.
deedub84-Trigger-excel-vba-routi.zip
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37836494
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37836762
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
 
LVL 1

Author Closing Comment

by:deedub84
ID: 37915332
Perfect thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now