Clicking on a cell...

[KennytheFish]

Hi
Is it possible to initiate a formula by clicking on a cell? I can't seem to find any information on doing so...
Thanks

[NBVC]

Not sure exactly what you mean...please elaborate.

[VBA Noob]

Maybe with an event macro. What is your formula or scenario

VBA Noob

[KennytheFish]

Hi
A very basic example would be if I wanted to show the result (in B3) of B1*B2 by clicking on the * in B4, or B1+B2 by clicking on the + in B5... if that makes sense.....

_A B C D
1 ...5.....
2 ...3.....
3 .........
4 ...*....
5 ...+....

Thanks

[Paul]

Hi Kenny,

The following macro should work for you. Right-click on the sheet tab for which you want this functionality and choose View Code. Copy and paste the code below into the VB screen that appears. Close the VB screen and test.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Range("B4:B5"), Target) Is Nothing Then
        Exit Sub
    Else
        If Range("B1") = "" Or Range("B2") = "" Then
            MsgBox "Missing value in cell B1 or B2", vbOKOnly, "Oops!"
            Range("B3").Select
            Exit Sub
        Else
            Select Case Target.Text
                Case Is = "*"
                    Range("B3").Value = Range("B1").Value * Range("B2").Value
                Case Is = "+"
                    Range("B3").Value = Range("B1").Value + Range("B2").Value
                Case Else
                    MsgBox "Invalid operand"
            End Select
        End If
    End If
    Range("B3").Select
End Sub

This works by double-clicking one of the two cells (B4 or B5) after inputting numbers into B1 and B2. If either B1 or B2 is blank, the macro will let you know and then quit before performing any math. If you double-click a cell other than B4 or B5, the macro will quit and the sheet will act normally (putting you into the cell you double-clicked).

UPDATED CODE, was handling events in the wrong order.

[KennytheFish]

Thanks for the trouble of doing that, its really helped