Link Excel cells to run macro

[perryarrow]

I want to link a cell in Excel sheet so that it runs a macro that I have created. The macro takes the text in that cell and uses it.
I have the macro that does this but have not found a way to run it by clicking in a cell like you would for a Hyperlink.

Any ideas would be appreciated.

 

[SkipVought]

You could use the Woeksheet_SelectionChange event and test the Target range to see if it intersects with your cell...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim rng as Range
   Set rng = Application.Intersect(Target, Range("YourRange"))
   If Not rng Is Nothing Then
   'Houston, we got a hit!
      YourMacro
   End If
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[pinto901]

You could create a button if you like so that when you click it it will run the macro with the sub


Private Sub btnZoomIn_Click()
ZoomIn 'the macro
End Sub

btnzoomin is the name of the button

 

[dfarland]

Just put a button on the sheet the same size as the cell. Then you can link your macro to that button. You will find the buttons on the forms toolbar. After you put your button on the sheet, right click on it and choose "Assign Macro", pick your macro and click OK. You can also put text on there, make it any color you want. Very useful, try it.

 

[perryarrow]

Thank you for the input.
Skip, I have a question on your suggestion. Bear with me since I am new to this. The following is what I understand your code would do. Your code + my code would be started any time any cell is clicked? If the clicked cell is not in my defined range, the macro does nothing. If the clicked cell is in my defined range it will execute my inserted code.
In this scheme the macro gets called or started any time a cell is selected?
If my defined range is a column then any time someone clicks one of the column's rows my code would would get executed?
I will try suggestions and see what happens.
Thanks again!

 

[SkipVought]

If your column range intersects with the Target range, YourMacro would execute.

But that raises an interesting question that my example did NOT address. What if your Column range were "B" and the user selects Row 1. Do you want YourMacro to execute? It can, with a modification --

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim rng as Range, t as Range
   For each t in Target
     Set rng = Application.Intersect(t, Range("YourRange"))
     If Not rng Is Nothing Then
     'Houston, we got a hit!
        YourMacro
        Exit Sub 
     End If
   Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Actually, the original works IF the execution is not based on a particular intersection. If the question is merely...

Is there an Intersection? Yes/No

then the first solution will suffice.

But if the question is...

Is there an Intersection, and If Yes then using that Intersect information...

Get my drift?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[perryarrow]

I must be missing something. The Private Sub that you provided, where does it get put? In the same module of an already defined macro? And does this already defined macro have to be running?
Or Should this Private sub be stand alone in its own module?
Then it does not have a macro name?

The idea is that the text in the rows of a column are names of files. When the user clicks on one of the cells in the column, an Open file dialog pops up with a filter that only shows files who's names starts with the text that was in the selected cell.

 

[SkipVought]

In the VB Editor, there is a Project Explorer -- View/ProjectExplorer.

In the PE are the Objects in the project. There is an object for each Worksheet. If you select the Worksheet in question, the Code Window will show the VB Code for that Worksheet.

In the Upper-LH corner of the Code Window is the Object Dropdown. Select Worksheet.

In the Upper-RH corner is the Procedure Dropdown. Select Change (or whatever event you want to perform a procedure in. This is where this code goes. Whenever the worksheet changes for a user entry, this procedure will execute.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Sorry

SelectionChange

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[perryarrow]

Ahhhhh!
Skip, Your first code works.
Thanks for the guidance.
Although I am not sure if the idea is exactly what is needed.
If you, the user, want to edit the text in the cell the macro keeps happening. Need to do same action maybe with a double click.
More playing required.
Yeha!

 

[SkipVought]

It only occurs when the selection changes. So if the Column is "B" and the user selects B4, then before the user makes any changes, YourMacro will run. The macro will not run again until another cell in column B is selected.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[waynerenaud]

You could try this (assumes your cell would be G3, change as appropriate)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = "$G$3" Then
'Your macro here 'module1.macro1
End If
End Sub

 

[Cresby]

There is a more general

Private Sub object_SheetChange(ByVal Sh As Object, ByVal Source As Range)
blah blah blah

end sub


Which I have used. You have to right click on the sheet tab and select "view code" then insert the code above and fill with your code which you could cut and paste from a recorded macro (Tools>recordMacro>)do something(Tools>stoprecording) (Tools>MAcro>macroname>edit) hit edit not run!!!! Cut and past into sheet macro

Good luck

 

[Cresby]

object = name of sheet