Note: The other languages of the website are Google-translated. Back to English

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:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (37)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Trigger Or Run A Macro By Clicking A Specific Cell With VBA Code, couldn't get this to work. I tried it many different way, but it never acheived what was promised.
This comment was minimized by the moderator on the site
This code will only work if you place it in the "ThisWorkbook" module. It does not work on a general module.
workbookname > Microsft Excel Objects > ThisWorkbook.
This comment was minimized by the moderator on the site
This code will work on worksheet code module. You need to right click on sheet tab and click on 'View Code'. This will open code module for that worksheet only. Then paste the mentioned code.
This comment was minimized by the moderator on the site
If you want to have multiple macros, cells running different macros on the same page - is that possible?
This comment was minimized by the moderator on the site
Dear Simon,
The below VBA script can help you to run different macros by clicking on cells on the same page.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("D4")) Is Nothing Then Call MyMacro1
If Not Intersect(Target, Range("D8")) Is Nothing Then Call MyMacro2
If Not Intersect(Target, Range("D10")) Is Nothing Then Call MyMacro3
End If
End Sub

Please add line "If Not Intersect(Target, Range("D10")) Is Nothing Then Call MyMacro" to run more macro by clicking cell. And change the cell and macro names in the code based on your needs.
This comment was minimized by the moderator on the site
It isn't working on my Excel. The code is correct?
This comment was minimized by the moderator on the site
Hi Camila,
Sorry for the inconvenience. Try the below VBA code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRgArr As Variant
Dim xFunArr As Variant
Dim xFNum As Integer
Dim xStr As String
Dim xRg As Range
xRgArr = Array("A1", "D1", "C1") 'Cells used to trigger macro
xFunArr = Array("Code name1", "Code name2", "Code name3") 'The corresponding code names
If Selection.Count = 1 Then
For xFNum = 0 To UBound(xRgArr)
Set xRg = ActiveSheet.Range(xRgArr(xFNum))
If Not Intersect(Target, xRg) Is Nothing Then
xStr = xFunArr(xFNum)
Application.Run xStr
End If
Next
End If
End Sub
This comment was minimized by the moderator on the site
This worked perfectly and will save me a load of time - thank you for sharing your knowledge - much appreciated!
This comment was minimized by the moderator on the site
I’m glad I could help.
This comment was minimized by the moderator on the site
Great. Its working well... Thank you....
This comment was minimized by the moderator on the site
Hi.

I have this working well, but would like to add a condition to running the macro. I only want to run the macro if the cell alongside the cell I click into contains a certain value.
e.g. When I click on cell F6, I want the macro to run if cell E6 contains "x", but if cell E6 is blank, the macro must not run.
Hope that makes sense.
Thanks

here is my original code without the condition:


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("F6:F18")) Is Nothing Then
Call datePick
End If
End If
End Sub
This comment was minimized by the moderator on the site
Hi,
The following VBA code can help you solve the problem. Please have a try and thank you for your comment.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
If Not Intersect(Target, Range("F6:F18")) Is Nothing Then
Set xRg = ActiveSheet.Cells(Target.Row, Target.Column - 1)
If (xRg.Value = "") Or (xRg.Value <> "X") Then Exit Sub
Call datepick
End If
End Sub
This comment was minimized by the moderator on the site
Thanks but what about merged cells?
This comment was minimized by the moderator on the site
Hi Alber,
The code does not work for merged cells.
This comment was minimized by the moderator on the site
i'm using OpenOffice and have right clicked on sheet-tab and selected events and then selected this macro from MyMacros.. However I get and error on the following line: If Selection.Count = 1 Then >>> "Basic runtime error, variable not defined...
This comment was minimized by the moderator on the site
Hi James,
The code only works for Microsoft Office Excel. Thank you for your comment.
This comment was minimized by the moderator on the site
My full code in this macro is:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim val As String
REM val = Range("A2").Value

If Selection.Count = 1 Then
If Not Intersect(Target, Range("D24")) Is Nothing Then
REM Call MyMacro
val = Range("D24").Value
Range("B27").Value = val
End If
End If
End Sub
This comment was minimized by the moderator on the site
This topic is very interesting and I am interested but do not know where to find, thankfully you create this topic, hope everyone will help me http://run-3.online
This comment was minimized by the moderator on the site
Excel 2002 (XP): If a workbook is opened by selecting "File" and pressing "Shift" it disables macros in that sheet, the problem is that if I select "Tools > Macro > Macros..." I can run the macro anyway, how to solve?
This comment was minimized by the moderator on the site
Hi Roger,
We haven't tested the code in Excel 2002(XP). Why not use the newer version of Microsoft Office? It will be more easy for your work.
This comment was minimized by the moderator on the site
Same result in 2010.
This comment was minimized by the moderator on the site
Why not just use some newer version it will be more easy
https://games.lol/racing/
This comment was minimized by the moderator on the site
Thanks, but how about several cells for clicking for running several macros.
This comment was minimized by the moderator on the site
Hi,
Try the below VBA code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRgArr As Variant
Dim xFunArr As Variant
Dim xFNum As Integer
Dim xStr As String
Dim xRg As Range
xRgArr = Array("A1", "D1", "C1") 'Cells used to trigger macro
xFunArr = Array("Code name1", "Code name2", "Code name3") 'The corresponding code names
If Selection.Count = 1 Then
For xFNum = 0 To UBound(xRgArr)
Set xRg = ActiveSheet.Range(xRgArr(xFNum))
If Not Intersect(Target, xRg) Is Nothing Then
xStr = xFunArr(xFNum)
Application.Run xStr
End If
Next
End If
End Sub
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?
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.
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
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.
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).
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.
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
jnw.wells@gmail.com
This comment was minimized by the moderator on the site
Hi John Wells,Thank you for sharing.
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.
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
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.
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.
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.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL