Implied Circular Reference ... in w/s SelectionChange Event

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Guest]

Tom;

I tried your Change event code, but it didn't produce the desired results.
I've a strong feeling that it should be a SelectionChange event, and my
earlier code is simply missing a statement or something (to fix that little
glitch!).

Any suggestions? Thank you.

 

[JE McGimpsey]

Your "strong feeling" is misplaced. SelectionChange is the wrong event,
since it fires when the Selection is changed, not when the value of a
cell is changed.

For instance, if G7:I7 were all selected, you could make changes to G7
or I7 and SelectionChange never fires.

Likewise, if your preferences are set to move the active cell down one
row when you hit Enter, then making a change in G6 and hitting enter
will cause the SelectionChange event to fire, and G7 will be returned as
the Target, even though the change was made in G6.

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
If .Column = 7 Then
.Offset(0, 2).Value = .Value / 50
ElseIf .Column = 9 Then
.Offset(0, -2).Value = .Value * 50
Else
'not column G or I
End If
End With
ErrHandler:
Application.EnableEvents = True
End Sub

It assumes, since you didn't give much information about any other entry
cells, that any entry in column G should result in a value in the
corresponding row in column I of entry/50. Likewise any entry in column
I will produce a value in the corresponding row of column G of entry *
50.

 

[Guest]

While I'm testing your event code, please keep in mind that there are similar
input pairs in G and I columns at different rows, each pair is related by a
different factor. For Example:
...........cells G7 and I7..........factor 50.
...........cells G13 and I13.......factor 100.
...........cells G14 and I14.......factor 300.
...........cells G33 and I33 ......factor 10.

Only those cells on the w/s should be affected by the Change or the
SelectionChange event.

Changing or selecting other cells on the sheet shouldn't be impacted by the
event.

Thank you.

 

[JE McGimpsey]

It would be difficult to "keep in mind" information that you haven't
posted previously in the thread...

Your additional information just requires revising the macro a bit.
While there are myriad ways to do it, here's one:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sInputCells = "G7,I7,G13,I13,G14,I14,G33,I33"
Dim dFactor As Double
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range(sInputCells)) Is Nothing Then
Select Case .Row
Case 7
dFactor = 50
Case 13
dFactor = 100
Case 14
dFactor = 300
Case 33
dFactor = 10
End Select
On Error GoTo ErrHandler
Application.EnableEvents = False
If .Column = 7 Then
.Offset(0, 2).Value = .Value / dFactor
ElseIf .Column = 9 Then
.Offset(0, -2).Value = .Value * dFactor
End If
End If
End With
ErrHandler:
Application.EnableEvents = True
End Sub

Of course, this assumes that there isn't additional information that
would require further modification.

Give up on SelectionChange - it will never do what you're asking for.

 

[Guest]

JE McGimpsey;

Your code works absolutely perfect! and it does exactly what I'm asking for.

Here is just a thought. If, for example, the value of "dFactor" is the same
for ""Case 7" and "Case 33" in your code, Can I combine both statements?
This would be helpful in situations where, for example, there are say 30
paired cells but only a few values of "dFactor".

Once again, thank you kindly for your tremendous help and patience.
Clrealy, you're very knowledgeable and very experienced on the subject matter.

..

 

[Guest]

Simply include the list of rows in the common denominator "Case" statement.
If, for example, "dFactor = 50." for "Case 7" and "Case 33", then "Case 7"
line in the w/s Change event code would read: "Case 7, 33", and delete "Case
33". I was'nt aware of the Case expression list!

Thanks again for your help