Creating a Stop for Worksheet Calculate Event

[rickincanada]

Hi there!

I'm a fairly new VBA user and am attempting use the Worksheet Calculate Event to fire an Advanced Filter macro that I have. My trouble is that once the worksheet calculates, the macro runs which then causes the worksheet to calculate again creating an infinite loop.

Here's what I was thinking I could do however it isn't working...

Private Sub Worksheet_Calculate() 'using worksheet "Deal Info"
If Range("B36").Value = 0 Then
Call OptionFilter
Range("B36").Value = 1
End If
Range("B36").Value = 0
End Sub

As you can see I'm trying to get the function to look at a cell value and if it is 0 then run and if it doesn't then stop. I'd run the macro and then upon completion place a 1 in the cell that is designed to stop it. I can see how this logic fails however I'm not sure how to best complete this task.

Please help! Thanks.

 

[Jaafar Tribak]

Hi there!

I'm a fairly new VBA user and am attempting use the Worksheet Calculate Event to fire an Advanced Filter macro that I have. My trouble is that once the worksheet calculates, the macro runs which then causes the worksheet to calculate again creating an infinite loop.

Here's what I was thinking I could do however it isn't working...

Private Sub Worksheet_Calculate() 'using worksheet "Deal Info"
If Range("B36").Value = 0 Then
Call OptionFilter
Range("B36").Value = 1
End If
Range("B36").Value = 0
End Sub

As you can see I'm trying to get the function to look at a cell value and if it is 0 then run and if it doesn't then stop. I'd run the macro and then upon completion place a 1 in the cell that is designed to stop it. I can see how this logic fails however I'm not sure how to best complete this task.

Please help! Thanks.

Does this work for you ?

Private Sub Worksheet_Calculate() 'using worksheet "Deal Info"
[B]Application.EnableEvents=False[/B]
If Range("B36").Value = 0 Then
Call OptionFilter
Range("B36").Value = 1
End If
Range("B36").Value = 0
[B]Application.EnableEvents=True[/B]
End Sub

 

[rickincanada]

Problem solved! Thanks a million!