Run Excel macro by clicking a designated cell

Hello,

In Excel (2007), is there a way to run a macro by clicking a particular cell?  

In the documentation (Excel Help), I found descriptions re how to trigger macros by pressing keyboard combinations, clicking objects, clicking an icon on the Quick Access Toolbar and even by opening a workbook (see screenshot).  
 1
However, is there a way to have a designated cell (or its contents) behave almost as though it is a hyperlink -- but instead of going to a link destination, have it run a macro?

Thanks
Steve_BradyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
You can use SelectionChange event in the worksheet's code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count = 1 And Target.Address = "$A$1" Then
        MsgBox "A1 selected."
    End If

End Sub

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantCommented:
You can also use the FollowHyperlink event handler to intercept a hyperlink click:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    If Target.Range.Address = "$A$1" Then
        MsgBox "Hyperlink in A1 clicked."
    End If

End Sub

Kevin
0
Steve_BradyAuthor Commented:
Thanks Kevin,

Due to my remedial VBA knowledge, would you mind showing me how to put your code with the following simple macro?

In the Name Manager:

    define cell EZ40000 (Sheet3) to have the name, "FIRST"
    define cell D55 (Sheet3) to have the name, "TARGET"

Regardless of the point of origin, the macro should simply:

    jump to FIRST then jump to TARGET

That's it.

I've got a follow-up question that I willk ask in another thread.

Thanks.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Michael FowlerSolutions ConsultantCommented:
Is this what you are after.

To use it

Open visual basic editor (ALT+F9) and double click on the entry for the worksheet you want it to run and then cust and paste in the code below

Michael
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count = 1 And Target.Address = "$A$1" Then
        Range("EZ40000").Name = "FIRST"
        Range("EZ40000").Select
        Range("D55").Name = "TARGET"
        Range("D55").Select
    End If

End Sub

Open in new window

0
zorvek (Kevin Jones)ConsultantCommented:
Assuming you have added the two defined names using the Name Manager, this macro will do the selecting:

Public Sub SteveSaysDoThis()
    ThisWorkbook.Sheets("Sheet3").Range("FIRST").Activate
    ThisWorkbook.Sheets("Sheet3").Range("TARGET").Activate
End Sub

Why are you simply selecting two cells in sequence?

Kevin
0
Steve_BradyAuthor Commented:
Thanks for the responses.

zorvek:
>>Assuming you have added the two defined names using the Name Manager, this macro will do the selecting:


Thanks for the reply Kevin. However, with your code, I cannot determine how to apply it to a specific trigger cell.

I defined both names using the Name Manager and then pasted your code into a newly inserted Module.  I then saved as a .xlsm file. Next I clicked Macro in the Developer tab to open the Macro box. The macro was there but I could see no way to insert it into the cell.  That's the main thing I am looking for: how to be able to run it by simply clicking a cell or its contents.

I did click the Run button just for fun but that resulted in an error box with the following notice:


Run-time error '1004':

Activate method of Range class failed

0
Steve_BradyAuthor Commented:
zorvek:
>>Why are you simply selecting two cells in sequence?


I knew you would be wondering about that.  That's the follow-up question I am posting in another thread:  

Consistently have a hyperlink-targeted cell appear in the upper-left corner in Excel

Thanks
0
zorvek (Kevin Jones)ConsultantCommented:
You have to place the macro code in the code for the worksheet, not in a general code module.

To add VBA code to a worksheet or chart code module in an Excel workbook, right-click on the worksheet or chart tab at the bottom of the window and select View Code. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook. To find a worksheet or chart module when already in the VBE, press CTRL+R to open the VBE project explorer. Find the module in which the code will be placed - each worksheet and chart module is pre-assigned a name such as "Sheet1 (Sheet1)" where the name inside the parenthesis is the tab name. Double-click the desired module and paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Kevin
0
Steve_BradyAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.