Solved

Trigger excel vba routine only when cursor leaves range?

Posted on 2012-04-11
7
317 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 42

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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