Click in cell to run macro

[alistew]

Is it possible to click in a cell in Excel to run a macro? assign macro to
cell type thing.

 

[JE McGimpsey]

You can use the Worksheet_SelectionChange() event (put this in the
worksheet code module: right-click the worksheet tab and choose View
Code).


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address(False, False) = "A1" Then MyMacro
End Sub

But this will also be run when you tab/arrow into the cell. You might
want to use the double-click event instead:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Address(False, False) = "J10" Then
MyMacro
Cancel = True
End If
End Sub

 

[Gord Dibben]

You could use doubleclick event code.

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
'Substitute your cells/macro names.
Select Case Target.Address(False, False)
Case "A1"
Cancel = True
MyA1Macro
Case "J10"
Cancel = True
MyJ10Macro
Case "AB275"
Cancel = True
MyAB275Macro
End Select
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP

 

[alistew]

thanks was a great help!

You could use doubleclick event code.

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
'Substitute your cells/macro names.
Select Case Target.Address(False, False)
Case "A1"
Cancel = True
MyA1Macro
Case "J10"
Cancel = True
MyJ10Macro
Case "AB275"
Cancel = True
MyAB275Macro
End Select
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP

 

[alistew]

thanks so much this was a great help

You can use the Worksheet_SelectionChange() event (put this in the
worksheet code module: right-click the worksheet tab and choose View
Code).


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address(False, False) = "A1" Then MyMacro
End Sub

But this will also be run when you tab/arrow into the cell. You might
want to use the double-click event instead:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Address(False, False) = "J10" Then
MyMacro
Cancel = True
End If
End Sub

 

[alistew]

hi

im back again!

is it possible to use this code on merged cells? i am unsure how merged
cells are recognised. if i merge a1:d1, if you select it the name box will
display it as a1 but this is not recognised in the code. if i enter a1:d1 in
the code this is not recognised. any ideas????

 

[JE McGimpsey]

Well, aside from the fact that you shouldn't be using merged cells
(they're the spawn of the devil, and cause innumerable problems with
formatting, sorting, copying and pasting, etc), this may work for you:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
MsgBox Target.Address
'Substitute your cells/macro names.
With Target
If Not Intersect(Range("A1"), .Cells) Is Nothing Then
Cancel = True
MyA1Macro
ElseIf Not Intersect(Range("J10"), .Cells) Is Nothing Then
Cancel = True
MyJ10Macro
ElseIf Not Intersect(Range("AB275"), .Cells) Is Nothing Then
Cancel = True
MyAB275Macro
End If
End With
End Sub

 

[Gord Dibben]

I don't have an answer for the "merged cells" problem other than to
say........don't use that feature which causes no end of problems.

If A11 are merged to accommodate a title, use Center Across Selection instead.


Gord