Excel - Running a Macro instead of a Hyperlink 1

[basil3legs]

Hi,

Similar to this has been asked before but I can't seem to find a definitive answer so here goes (again!)

Is it possible to set up a cell in Excel 2000 so that when it is clicked on it runs a Macro i.e. similar to adding a Hyperlink.

Thanks in advance.

 

[eHanSolo]

HI there,

I'm not an expert but I'm pretty sure there isn't a built in excel function to do this. Also, it's pretty difficult to do if not impossible using VBA (someone correct me if i'm wrong).

I can only think of a workaround which achieves something similar. Try this:

Highlight the cell you want and then create a hyperlink which will open up an excel workbook.
This excel workbook can be hidden (probably best) which will automatically execute the macro you want running.
You can change the 'appearance' of the hyperlink to whatever you like... or you can change the font colour to the background colour and 'hide' it that way... etc etc

Not a bad workaround don't you think??

Regards,

E

 

[ilses]

Hi Basil,

Perhaps something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("a1:a10")) _
            Is Nothing Then
                MsgBox "Run a macro"
End If
End Sub

Replace "A1:A10" with your range.

Ilse

 

[basil3legs]

eHanSolo,

Thanks for the reply and it sounds a good workaround. Unfortunately, there will be about 10,000 of these cells on one page each needing to call one of about 30 Macros with different variables.

ilses, again thanks for the reply but I am totally confused now! Is this code to go with eHanSolo's suggestion or something totally seperate, in which case what do I do with it?

 

[ilses]

Hi Basil,

That code is separate from eHanSolo's suggestion.

It is an example of Worksheet event code for a sheet, it checks every time the selection on that sheet is changed (e.g. by clicking another cell) if the new selection (e.g. that other cell) is part of the range you specify. If it is, the example produces a message box. Of course, you'd have to change that to a call to your macro.

Go to the macro editor, double-click the sheet on which you have cells that have to run a macro.
At the top of the right-hand pane, use the left drop-down to select Worksheet. The first and last line for the Selection_change event are automatically added.

HTH

Ilse

 

[basil3legs]

Thanks Ilse, I think I can make use of this by putting the variables etc. in a cell off the page in the same row as the cell I want the Macro to run from.

Definitely worth a star ;-)