VBA if cell in range is selected then call macro

Future_MVP

New Member
Joined
Jan 16, 2009
Messages
5
If a cell in range is selected or changed I would like a macro to run. For example, If any cell is selected or changed in range("a2:c20") then (macro). Any help would be much appreciated. Thanks in advance.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A2:C20"     '<== change to suit

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
        
            Call YourMacro
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
 

ThomasB

Active Member
Joined
May 2, 2007
Messages
314
Hi

Try

Place this code in the sheet where the range is situated

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
Else
msgbox "Run Macro"
macro1
End If
End Sub

the range in this case is A1:10
you can take out the msgbox code if you like
macro1 is the name of the macro to be run subsitute with the macro name you wish to run

Rgs

Thomas
 

akash

New Member
Joined
Jan 21, 2009
Messages
3
which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste
 

Landcruiser87

New Member
Joined
Feb 7, 2008
Messages
16

ADVERTISEMENT

Hey Ya'll,

I was looking through this post for similar resolution to a cell exit macro, but one thing i can't figure out about the Worksheet_SelectionChange option is that it runs on EVERY cell i click for that page. I only want it to run for just one cell range. this means that when i run a fairly intensive macro over that page, it keeps referencing the Worksheet_SelectionChange macro and slowing down my performance.



any Takers?
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
It has to run on every cell change otherwise how will it know when to run?

You can control which cells it processes as I show in my example.
 

Landcruiser87

New Member
Joined
Feb 7, 2008
Messages
16
Truly, i timed it and it doesn't eat up that much time but i'm just trying to make it as streamlined as possible. Thanks for your reply though!

Is it possible to do a cell exit macro? Kind of like access does with all of its forms, text boxes, flying elephants, etc?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,099
Messages
5,570,210
Members
412,309
Latest member
Benky
Top