Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Implied Circular Reference ... in w/s SelectionChange Event
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Implied Circular Reference ... in w/s SelectionChange Event
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. "Tom Ogilvy" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Implied Circular Reference ... in w/s SelectionChange Event
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. In article , monir wrote: 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!). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Implied Circular Reference ... in w/s SelectionChange Event
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" wrote: 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. In article , monir wrote: 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!). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Implied Circular Reference ... in w/s SelectionChange Event
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. In article , monir wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Implied Circular Reference ... in w/s SelectionChange Event
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. .. "JE McGimpsey" wrote: 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. In article , monir wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Implied Circular Reference ... in w/s SelectionChange Event
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 "monir" wrote: 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. . "JE McGimpsey" wrote: 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. In article , monir wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change cell event using circular reference - not VBA! | Excel Worksheet Functions | |||
change cell event using circular reference | Excel Worksheet Functions | |||
SelectionChange event | Excel Programming | |||
SelectionChange event | Excel Programming | |||
SelectionChange Event | Excel Programming |