How to use a cell text to call a macro ?

  • Thread starter Thread starter Peter van de Kerkhof
  • Start date Start date
P

Peter van de Kerkhof

Hi,

Is is possible to call a macro using a text in a cell
e.g cell text: Click here to update the data
and clicking will start macro: UpdateData

or something like that.
(Somebode already showed me how to do it with a picture!)

Regards Peter
 
You can use the Worksheet_Selectionchange event to take action when a
specific cell is selected - it will fire whenever any cell is selected, but
you can test in the macro which cell selection triggered the macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$B$9" Then
MsgBox "You selected cell B9"
End If
End Sub

If entered in the sheet module of the sheet where you want the behavior
then:

This would fire whenever B9 was selected, but would not be restricted to
just clicking in the cell.

There is no built in method just to trap a mouse click on a cell - you might
kludge a solution using an invisible rectangle or something placed over the
cell.
 
How about a double click?

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

If Intersect(Target, Range("b4")) Is Nothing Then Exit Sub

If IsEmpty(Target) Then Exit Sub

Cancel = True 'stop editing in cell

On Error Resume Next
Application.Run Target.Value
If Err.Number <> 0 Then
MsgBox "didn't work"
Err.Clear
End If
On Error GoTo 0

End Sub

Right click on the worksheet tab that should have this behavior and paste that
it. Make sure you have a corresponding macro in one of your project's general
modules.

John Walkenbach suggests a technique of putting a rectangle over the cell and
assigning the macro to that rectangle click.

(like you did with the picture.)

http://j-walk.com/ss/excel/tips/tip12.htm
 
Hi Peter van de Kerkhof,

You can put a transparent object over the cell, a rectangle for example, and
assign a macro to this object.

HTH
 
Back
Top