Implied Circular Reference ... in w/s SelectionChange Event
Change to the Change event instead of Selection Change
Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
Application.EnableEvents = False
If ActiveCell.Row = 7 Then
if Range("I7").HasFormula = False then
ActiveCell.Offset(0, 2).Formula = "=" & _
ActiveCell.Address(False, False) & "/50."
ElseIf ActiveCell.Column = 9 Then
ActiveCell.Offset(0, -2).Formula = "=" & _
ActiveCell.Address(False, False) & "*50."
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub
--
Regards,
Tom Ogilvy
"monir" wrote in message
...
Hello;
On a w/s, there are a dozen or so input-related cells. Let us concentrate
on cells G7 and I7.
If I enter or change the value in cell G7, the value in I7 should be
"=I7/50.".
If I enter or change the value in cell I7, the value in G7 should be
"=I7*50.".
I used w/s SelectionChange Event to do the trick! Here is a sample code
for
the two related cells G7 and I7.
==========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Application.CommandBars("Circular Reference").Visible = False
'
If ActiveCell.Row = 7 Then
If ActiveCell.Column = 7 Then
ActiveCell.Offset(0, 2).Formula = "=" & ActiveCell.Address(False,
False) & "/50."
ElseIf ActiveCell.Column = 9 Then
ActiveCell.Offset(0, -2).Formula = "=" & ActiveCell.Address(False,
False) & "*50."
End If
End If
End Sub
=========================================
The above code works fine, but with a glitch!
Enter a value in cell G7, and cell I7 would show the correct value.
Now, select cell I7, and the value in G7 would show 0.00.
Change the value in cell I7, and the value in G7 would be correct again!
If you select either cell, but don't change its value, the other cell
would
show 0.00.
Your suggestion(s) would be greatly appreciated.
Thank you kindly.
|