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.

thanks

[royUK]

You can't assign a macro to cell, you could activate it by various worksheet events such as Double Click

[alistew]

Thanks for that.

How do you assign it to a double click event?

[royUK]

Open the VBE (Alt+F11) and then double click the Sheet in the Project Explorer the code applies to. This is where the code MUST be placed.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    Call MyMacro 
End Sub 

Replace MyMacro with your macro's name

[alistew]

thanks so much for that. you have saved my life!!!!

[alistew]

can you use this method for different macros and different cells etc... if i was to double click in a1 then e1 is pink, if i double click in b1 then f1 is red?? i was trying with an if statement using the activecell range but can get it working.

[royUK]

is this what you mean?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    Dim i As Integer 
    If Target.Cells.Count > 1 Then Exit Sub 
    i = Target.Column 
     
    Select Case i 
    Case 1: ActiveCell.Offset(0, 5).Interior.ColorIndex = 5 
    Case 2: ActiveCell.Offset(0, 5).Interior.ColorIndex = 3 
    Case 3: ActiveCell.Offset(0, 5).Interior.ColorIndex = 12 
    End Select 
     
End Sub 

[alistew]

thats great.thanks sooooooooooooooo much