Solved

Trigger excel vba routine only when cursor leaves range?

Posted on 2012-04-11
7
314 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

861 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

24 Experts available now in Live!

Get 1:1 Help Now