Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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


Combine multiple worksheets/workbooks into one worksheet / workbook:

Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now


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:



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Shahrokh · 1 months ago
    Thanks, but how about several cells for clicking for running several macros.
    • To post as a guest, your comment is unpublished.
      crystal · 8 days ago
      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
  • To post as a guest, your comment is unpublished.
    adam · 6 months ago
    Why not just use some newer version it will be more easy
    https://games.lol/racing/
  • To post as a guest, your comment is unpublished.
    Roger · 6 months ago
    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?
    • To post as a guest, your comment is unpublished.
      crystal · 5 months ago
      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.
  • To post as a guest, your comment is unpublished.
    James Clements · 7 months ago
    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
  • To post as a guest, your comment is unpublished.
    James Clements · 7 months ago
    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...
    • To post as a guest, your comment is unpublished.
      crystal · 5 months ago
      Hi James,
      The code only works for Microsoft Office Excel. Thank you for your comment.