CODEWITHSUNDEEP
excelvba
user3147294
user3147294

Reputation: 11

worksheet_change function causing loop

Firstly, as you may notice from below, my VBA skills are very limited!

I am creating a spreadsheet and i'm trying to include some sanity checking. Basically, if cell G69 is greater than zero then it means that an item has been bought, and therefore cell G68 (which defines the type of item that has been bought) cannot equal none, it must be a value (I force it to be 'eggs'). if G69 is not greater than zero then nothing's been bought and so G68 should be forced to 'none'.

Similarly, if cell G68 is set to 'none', then nothing's being bought so G69 should be forced to zero.

the code i've cobbled together for this is below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$G$69" Then

 If Range("$G$69").Value > 0 Then

   Range("$g$68").Value = "Eggs"

 Else: Range("$g$68").Value = "None"

End If

End If

If Target.Address = "$G$68" Then

 If Range("$G$68").Value = "None" Then

  Range("$g$69").Value = 0

 End If

End If

End Sub

I keep getting errors when running the macro, and I think it's because it creates a loop - I change the value of G69, as a result it changes the value of G68, which in turn triggers the if routine for that cell changing and therefore it tries to set G69 to a value, and so on...

Can anyone explain what i'm doing wrong? I suspect it's quite simple to resolve and related to my clumsy coding! :)

hope that makes sense! Many thanks..

Pat

Upvotes: 0

Views: 6119

Answers (3)

user3147294
user3147294

Reputation: 11

thanks so much for your help. code now looks as below and appears to work, not sure whether I got the error checking correct though!? :)

thanks again.....

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$G$69" Then    

        On Error GoTo Lastline
        Application.EnableEvents = False

        If Range("$G$69").Value > 0 Then
            Range("$g$68").Value = "Hosted" 
        Else: Range("$g$68").Value = "None"
        End If

        Application.EnableEvents = True 
    End If

    If Target.Address = "$G$68" Then

        Application.EnableEvents = False
        If Range("$G$68").Value = "None" Then

            Range("$g$69").Value = 0
        End If

       Application.EnableEvents = True
    End If

Lastline:

    Application.EnableEvents = True
End Sub

Upvotes: 1

PatricK
PatricK

Reputation: 6433

You should only change when ALL conditions are met. Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$G$69" ' Quantity
            ' Zero Quantity but Item is not None
            If Target.Value = 0 And Range("$G$68").Value <> "None" Then
                Range("$G$68").Value = "None"
            End If
            ' Non-zero Quantity but Item is None
            If Target.Value <> 0 And Range("$G$68").Value = "None" Then
                Range("$G$68").Value = "Eggs"
            End If
        Case "$G$68" ' Item Name
            ' Item is None but Quantity is not zero
            If Target.Value = "None" And Range("$G$69").Value <> 0 Then
                Range("$G$69").Value = 0
            End If
    End Select
End Sub

But this check won't work if G69/G68 contains a formula and the related range changes.

Upvotes: 0

Bill N.
Bill N.

Reputation: 218

If you don't want changes being made to cells in your VBA to trigger events, then you can disable events prior to the change and reenable events after the change.

Application.EnableEvents = False

' code here will not trigger events.

Application.EnableEvents = True

Upvotes: 8

Related Questions