Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trigger excel vba routine only when cursor leaves range?

Posted on 2012-04-11
7
Medium Priority
?
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

670 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