Skip to main content

How to trigger or run a Macro by clicking a specific cell in Excel?

While working with Microsoft Excel, you may know how to run a certain Macro with a Command Button. But do you know how to run a Macro by just clicking on a specific cell in a worksheet? This article will show you method of triggering a Macro by clicking on a specific cell in details.

Trigger or run a Macro by clicking a specific cell with VBA code

Trigger or run a Macro by clicking a specific cell with VBA code

The following VBA code can help you to run a Macro by clicking a specific cell in Excel. Please do as follows.

1. On the worksheet with the cell you need to click to run a Macro, right click the sheet tab, and then click View Code from the context menu.

2. In the Microsoft Visual Basic for Applications window, copy and paste the below VBA script into the Code window.

VBA code: Trigger or run a Macro by clicking a specific cell

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then
            Call MyMacro
        End If
    End If
End Sub

Notes:

1. In the code, D4 is the cell you will click to run Macro;

2. Please replace the code name MyMacro with the Macro you will run in the worksheet. See screenshot:

3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.

From now on, when clicking on cell D4 in current worksheet, your specified Macro will be triggered immediately.

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (38)
No ratings yet. Be the first to rate!
Corrado
Corrado
This comment was minimized by the moderator on the site
Hi,
interesting, how can I adapt it for working on a cell that is a selection?
Basically I need to select a value from a cell (any value of a list) and then launch the macro, can you help me?
Thaks!
Corrado
Reply
0
0
Maca
Maca
This comment was minimized by the moderator on the site
Hola,

No encuentro la manera de hacer lo que necesito. Espero de veras que me podáis ayudar.
Necesito que se lance una MACRO al hacer clic en una celda, pero no sé en qué celda va a ser, no lo puedo comparar con (si se ha seleccionado la celda A3, por ejemplo, que se lance la acción) porque por funcionalidades del fichero, las celdas se van a mover de sitio (se han podido insertar, mas o menos filas encima). Necesitaría poder saber qué celda es la que se ha seleccionado.
Hay alguna manera de poder hacer eso?
Muchas gracias de antemano.
Reply
0
0
crystal
crystal     Maca
This comment was minimized by the moderator on the site
Hi Hola,
The cell address is constant. All you can move is the value of the cell.
The cell you specified in the VBA code is still the cell that trigger the specified VBA code.
Reply
Report
0
0
ivory howard
ivory howard
This comment was minimized by the moderator on the site
I didn’t have any expectations concerning that title, but the more I was astonished. The author did a great job. I spent a few minutes reading and checking the facts. Everything is very clear and understandable. I like posts that fill in your knowledge gaps. This one is of the sort.
Reply
0
0
Massimo
Massimo
This comment was minimized by the moderator on the site
Grandioso! Grazie, era una vita che sognavo di poterlo fare, se può essere utile a qualcuno avendo necessità di eseguire più codici su più celle per
aumentare il numero di opzioni basta aggiungere le nuove istruzionisempre sotto la stessa option explicit altrimente ci viene detto che il riferimento al
worksheet non è univoco. Mi spiego meglio evidentemente su come ho adattato il codice con il mio esempio;

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("Y64")) Is Nothing Then
Range("Y65:Y78").Select
Range("Y65").Activate
Selection.ClearContents
Range("Y65").Select
End If
If Not Intersect(Target, Range("A33")) Is Nothing Then
Range("A33").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
SendKeys "^v"
SendKeys "{BACKSPACE}"
End If
End If
End Sub

Grazie ancora è stata un'autentica meraviglia!

Massimo
Reply
0
0
Chris S
Chris S
This comment was minimized by the moderator on the site
For those wanting this to work for merged cells, the quickest and dirtiest way is to update Line 2 as follows:
<div data-tag="code">Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 0 Then
If Not Intersect(Target, Range("D4")) Is Nothing Then
Call MyMacro
End If
End If
End SubSelection.Count is being used to determine if a selection has been made before executing the remaining macro. If your selection consists of four cells that have been merged, Selection.Count will equal 4, and the remaining code will never execute.You can also change line 2 to the exact number of cells that were merged, but that will cause problems if you merge additional cells.
Reply
0
0
John Wells
John Wells
This comment was minimized by the moderator on the site
It is important to stress that the macro only runs in the worksheet.
Those of us who use the regular visual basic editor often end up storing macros in other places than in the current worksheet.
None of these codes will work in that situation.
I find this code to be much simpler and it works very well for me: 

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Range("d1"), Target) Is Nothing Then
Call aa
End If
End Sub
Sub aa() ' put your code here
Range("D1").Select
Selection.Copy
Range("F1").Select
ActiveSheet.PasteEnd Sub
John Wells
Reply
0
0
crystal
crystal     John Wells
This comment was minimized by the moderator on the site
Hi John Wells,Thank you for sharing.
Reply
Report
0
0
CodeKiller
CodeKiller
This comment was minimized by the moderator on the site
Does not work anymore.
Worksheet_SelectionChange does not exist by default. Meaning you cannot use that to trigger a macro.

The only embeded sub you can use are referenced in "Workbook" dropdown list, so...

The only closest thing you can do is using Workbook_SheetBeforeDoubleClick. But is will enter Edit mode (still not a big deal).
Reply
Report
0
0
crystal
crystal     CodeKiller
This comment was minimized by the moderator on the site
Hi CodeKiller.Worksheet_SelectionChange only exists in the Sheet(Code) editor. Right click the sheet tab and click View Code to activate the Sheet(Code) editor.
Reply
Report
0
0
Houra
Houra
This comment was minimized by the moderator on the site
Hello, Thank you for your interesting subject. What if for example I want to to click on D4 in Sheet1 and see the result of Macro in Sheet2!F3.
Reply
0
0
Justin
Justin
This comment was minimized by the moderator on the site
hello, I was trying to use this code to Run a paste macro but can't seem to figure out how to paste the macro properly.

here is my original Marco

Sub Paste()
'
' Paste Macro
'

'
Range("B34").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False, NoHTMLFormatting:=True
End Sub


any help is appreciated
Reply
0
0
M. Symonds
M. Symonds
This comment was minimized by the moderator on the site
Thanks for this code. Is it possible to get this to work by clicking on a cell that is merged with others?
Reply
0
0
crystal
crystal     M. Symonds
This comment was minimized by the moderator on the site
Hi M.Symonds,
The code in this article can do you a favor: https://www.extendoffice.com/documents/excel/4354-excel-click-on-cell-to-run-macro.html
Thank you for your comment.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations