CODEWITHSUNDEEP
excelvbaautomatic-properties
user22168531
user22168531

Reputation: 5

Automatically update cells

I currently have this code for my worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' ensure events are always enabled when we exit this Sub, even if we have an error
    On Error GoTo errExit
    Application.EnableEvents = False
    
    Dim SpotRate As Double
    SpotRate = Me.Range("H2").Value

    If Target.Address = "$C$3" Then
        If Target.Value <> "" Then
            Range("E3").Value = Target.Value * SpotRate
        Else
            Range("E3").ClearContents
        End If
    ElseIf Target.Address = "$E$3" Then
        If Target.Value <> "" Then
            Range("C3").Value = Target.Value / SpotRate
        Else
            Range("C3").ClearContents
        End If
    End If

errExit:
    Application.EnableEvents = True
End Sub

There is a pair a dropdown in cell C2 that contains information for the spot rate.

When I input the values into C3 or E3 the code works as required. However, if I change the value in cell C2 (which changes the spot rate in H2), these values don't automatically update in C3 or E3. Is there a way I can change this so that the value in C3 is held constant but E3 is automatically updated according to the new spot rate? Thanks.

Upvotes: 0

Views: 106

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, v, SpotRate As Double
    
    If Target.CountLarge > 1 Then Exit Sub 'only handling single-cell changes
     
    On Error GoTo errExit
    
    SpotRate = Me.Range("H2").Value
    
    Select Case Target.Address(False, False)
        Case "C2"
            'trigger updates via event handler
            Me.Range("C3").Value = Me.Range("C3").Value
            Me.Range("E3").Value = Me.Range("E3").Value
        Case "C3"
            Application.EnableEvents = False
            If Target.Value <> "" Then
                Range("E3").Value = Target.Value * SpotRate
            Else
                Range("E3").ClearContents
            End If
        Case "E3"
            Application.EnableEvents = False
            If Target.Value <> "" Then
                Range("C3").Value = Target.Value / SpotRate
            Else
                Range("C3").ClearContents
            End If
    End Select
        
errExit:
    If Err.number <> 0 Then Debug.Print Err.Description
    Application.EnableEvents = True
End Sub

Upvotes: 1

hadook
hadook

Reputation: 1

You have blocks of code that perform some actions when the Target (range that changed, triggering the Worksheet_Change event) address is "$C$3" and "$E$3".

If Target.Address = "$C$3" Then

ElseIf Target.Address = "$E$3" Then

You need an equivalent block that will perform the calculation if Target.Address = "$H$2".

Assuming my understanding of what you're trying to achieve is correct, this should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)

' ensure events are always enabled when we exit this Sub, even if we have an error
On Error GoTo errExit
Application.EnableEvents = False

Dim SpotRate As Double
SpotRate = Me.Range("H2").Value

'if H2 is not a number
If Not IsNumeric(SpotRate) Then
    Range("E3").ClearContents
    Range("C3").ClearContents
    Exit Sub
End If

'E3 changed
If Target.Address = "$E$3" Then
    'adjust E3
    If IsNumeric(Range("E3").Value) Then
        Range("E3").Value = Target.Value * SpotRate
    Else
        Range("E3").ClearContents
    End If
    
'C3 changed
ElseIf Target.Address = "$C$3" Then
    'adjust C3
    If IsNumeric(Me.Range("C3").Value) Then
        Range("C3").Value = Target.Value / SpotRate
    Else
        Range("C3").ClearContents
    End If
    
'C2 changed
ElseIf Target.Address = "$C$2" Then
    If IsNumeric(Me.Range("C2").Value) Then
        'adjust E3
        If IsNumeric(Range("E3").Value) Then
            Range("E3").Value = Target.Value * SpotRate
        Else
            Range("E3").ClearContents
        End If
        'adjust C3
        If IsNumeric(Me.Range("C3").Value) Then
            Range("C3").Value = Target.Value / SpotRate
        Else
            Range("C3").ClearContents
        End If
    Else
        'C2 is not a number
        Range("E3").ClearContents
        Range("C3").ClearContents
    End If
End If

errExit:
    Application.EnableEvents = True
End Sub

EDIT: Sorry, misuderstood the question at first, corrected my comment to include the right solution.

Upvotes: 0

Related Questions