Insert Formula based in cell user clicks

[aileen]

In need to insert a simple formula into Column J based on the cell in the
same row which is clicked by the user. Only one of the cells will be clicked
and color changed between the following cells F,H,L,M or N. The formula is
the product of 3 cells, 2 of which are fixed and the last one should become
whichever cell the user clicks.

e.g.

Cells(j, "J") = Cells(j, "E") * Cells(j, "I") * clicked cell(either cell F,
H, L, M or N)


Is this possible to do?

 

[Dave Peterson]

You could use a worksheet_selectionchange event. But that would fire each time
you changed the selection -- either by mouse or by arrow keys or by scrolling.
(There is no event for clicking on a cell.)

So how about using the _beforerightclick event instead?

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select View code and paste this into the code window that opened up
(usually on the right hand side).

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

Dim res As Double

With Target
If .Cells.Count > 1 Then
Exit Sub
End If

If Intersect(.Cells, Me.Range("F:F,H:H,L:N")) Is Nothing Then
Exit Sub
End If

Cancel = True 'stop popup menu from showing up

On Error Resume Next
res = Me.Cells(.Row, "E").Value _
* Me.Cells(.Row, "I").Value _
* .Value
If Err.Number <> 0 Then
Err.Clear
MsgBox "Failed--check for numbers!"
Else
Me.Cells(.Row, "J").Value = res
End If
End With

End Sub

 

[aileen]

I was able to get this working so no need to post a reply. Thanks.