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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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,122,829
Messages
5,598,341
Members
414,232
Latest member
MIA10_KO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top