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.
    Alber · 8 months ago
    Thanks but what about merged cells?
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Alber,
      The code does not work for merged cells.
  • To post as a guest, your comment is unpublished.
    Phil H · 8 months ago
    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
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      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
  • To post as a guest, your comment is unpublished.
    Medical Coder · 10 months ago
    Great. Its working well... Thank you....
  • To post as a guest, your comment is unpublished.
    Wayne · 1 years ago
    This worked perfectly and will save me a load of time - thank you for sharing your knowledge - much appreciated!
  • To post as a guest, your comment is unpublished.
    Simon Jones · 1 years ago
    If you want to have multiple macros, cells running different macros on the same page - is that possible?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      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.
      • To post as a guest, your comment is unpublished.
        Camila · 6 months ago
        It isn't working on my Excel. The code is correct?
        • To post as a guest, your comment is unpublished.
          crystal · 8 days ago
          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