CODEWITHSUNDEEP
excelvba
sorab
sorab

Reputation: 21

simultaneous input to 2 cells

I intend to create a worksheet in excel with two cells as inputs. For Ex. A2 is one; and C2 is five Relationship between A2:C2 = 1:5 Relationship between C2:A2 = 1:1/5 Is there a way to write a script to get this cyclic relationship. Next time I type 2 in (A2) I see that cell (C2) is 10 Also if I type 40 in (C2) I see 8 in (A2)

Upvotes: 2

Views: 52

Answers (2)

DisplayName
DisplayName

Reputation: 13386

You have to use both Worksheet_Change() (to react to specific cell value change) and Worksheet_SelectionChange() (to catch ratio before any of the specific cells value change happens) event handlers.

Thus place the following in the wanted sheet code pane:

Dim ratio As Double

Private Sub Worksheet_Change(ByVal Target As Range)    
    On Error GoTo exitSub
    Application.EnableEvents = False
    Select Case Target.Address
        Case "$A$2"
            Range("C2").Value = ratio * Target.Value2
        Case "$C$2"
            Range("A2").Value = ratio * Target.Value2
    End Select

exitSub:
    Application.EnableEvents = True    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
    Select Case Target.Address
        Case "$A$2"
            ratio = Range("C2") / Target.Value2
        Case "$C$2"
            ratio = Range("A2") / Target.Value2
    End Select    
End Sub

Upvotes: 1

user4039065
user4039065

Reputation:

Use a Worksheet_Change in the worksheet's private code sheet (right-click worksheet name tab, View Code).

Private Sub Worksheet_Change(ByVal Target As Range)

    'only A2 and C2
    'If Not Intersect(Range("A2, C2"), Target) Is Nothing Then
    'all of columns A and C
    If Not Intersect(Range("A:A, C:C"), Target) Is Nothing Then

        On Error GoTo safe_exit
        Application.EnableEvents = False
        Dim t As Range
        For Each t In Intersect(Range("A:A, C:C"), Target)
            select case t.column
                case 1
                    t.offset(0, 2) = 5 * t.value
                case 3
                    t.offset(0, -2) = t.value / 5
            end select
        Next t
    End If

safe_exit:
    Application.EnableEvents = true

End Sub

Upvotes: 4

Related Questions