Prev: Turn a range of cells into 'read only'
Next: programming 33 tabs with 1 chart on each, 9 series lines per chart
From: aileen on 27 Apr 2010 14:23
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?


From: Dave Peterson on 27 Apr 2010 15:34
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 wrote:
>
> 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
From: aileen on 27 Apr 2010 15:39
I was able to get this working so no need to post a reply. Thanks.

"aileen" wrote:

> 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?
>
>
 | 
Pages: 1
Prev: Turn a range of cells into 'read only'
Next: programming 33 tabs with 1 chart on each, 9 series lines per chart