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.
 

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

Forum statistics

Threads
1,082,284
Messages
5,364,282
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top