Data validation change triggers macro twice

[Skysurfer]

Hi,

I have created table where one field (a column of 100 rows) contains data validation. If the user enters the wrong selection in one of those cells, a cell on another sheet (named "CashAdvanceErrorTrigger") calculates to TRUE (there is an error). That triggers a msgbox poping up explaining the error to the user.

Problem is that the msgbox macro runs twice when triggered. It runs normally when I remove the data validation. So, something about data validation double fires the macro.

Here's my simple code that's in the sheet where the TRUE/FALSE calculation cell is located:

Private Sub Worksheet_Calculate()

'   Trigger msgbox on user entering an expense category for a cash advance
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
        
    If Sheet4.Range("CashAdvanceErrorTrigger").Value = True Then
        
            MsgBox "A cash advance is not an expense." & vbNewLine & vbNewLine & "For a cash advance, please select the '--'" & vbNewLine & "in the Expense Category dropdown box.", vbExclamation, "Input Error"

    End If
    
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
        
End Sub

Thanks for any guidance!

Lawrence

 

[Skysurfer]

Oh man, I'm so sorry...I should have repeated the 100 validation cells were in a column/field of a data table.

But, I did comment out the calc lines and still had the issue. I also commented out the EnableEvents lines and it didn't work either.

Do you think my 'barbaric' solution above is sane? Alternatively, I could convert the table to range in the macro, run the msgbox, then recreate the table. But that seems risky. This tine, little code is quite a logic challenge.

Lawrence

 

[JoeMo]

Oh man, I'm so sorry...I should have repeated the 100 validation cells were in a column/field of a data table.

But, I did comment out the calc lines and still had the issue. I also commented out the EnableEvents lines and it didn't work either.

Do you think my 'barbaric' solution above is sane? Alternatively, I could convert the table to range in the macro, run the msgbox, then recreate the table. But that seems risky. This tine, little code is quite a logic challenge.

Lawrence

I can't reproduce the problem: if I comment out both calc lines, and leave calc set to auto I only get a single appearance of the message box when my validation cells are in a table.

I see nothing risky about toggling the table to a range and back again, but then I'm not familiar with the content of those tables. Certainly, it's easy enough to try it.

 

[Skysurfer]

For future reference, I'm posting a solution to this problem. And sending out a special thank you to Joe for his time.

To stop looping through the code twice, I created a static variable that VBA can remember even after the macro exits. That variable increments by 1 when the macro runs the first time. On the 2nd run (triggering) of the macro, if the variable is greater than zero (meaning the macro has already run), the static variable to reset to zero and the macro exits.

Private Sub Worksheet_Calculate()

'   Create static variable to stop looping thru macro more than once
'   caused by data validation residing in a table which forces macro to run twice
    Static s As Integer

    s = s + 1
    
    If s > 1 Then
        End 'If macro run once then reset s = 0 and exit macro
        Exit Sub
    Else
'       Trigger msgbox on user entering an expense category for a cash advance
        If Sheet4.Range("CashAdvanceErrorTrigger").Value = True Then
        
            msgboxCashAdvanceErrorInput

        End If
    End If
    
End Sub

Lawrence