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

 

[JoeMo]

Do you have any other worksheet or Thisworkbook event code in place?

 

[Skysurfer]

Hi Joe,

Yes, I have the following code in ThisWorkbook:

Private Sub Workbook_Open()

'   Allow user to control outlining (grouping) on protected worksheets
    
    With Sheet2
        .Protect Password:="", Userinterfaceonly:=True
        .EnableOutlining = True
    End With
    
    With Sheet3
        .Protect Password:="", Userinterfaceonly:=True
        .EnableOutlining = True
    End With
    
    With Sheet7
        .Protect Password:="", Userinterfaceonly:=True
        .EnableOutlining = True
    End With
    
    With Sheet8
        .Protect Password:="", Userinterfaceonly:=True
        .EnableOutlining = True
    End With

    With Sheet9
        .Protect Password:="", Userinterfaceonly:=True
        .EnableOutlining = True
    End With

    With Sheet10
        .Protect Password:="", Userinterfaceonly:=True
        .EnableOutlining = True
    End With

End Sub

Plus some other minor code in a mode triggered by a command button on the sheet.

Thanks for the help!

Lawrence

 

[JoeMo]

I don't see anything that should cause the msgbox to appear twice and I can't reproduce that problem. Have you tried setting a break point on the first line and then stepping through the code after it triggers to see what's happening?

 

[Skysurfer]

Joe,

How do I set a 'break' point in a line of code? <sorry for the stupid questions?>

As for step through, can I let the macro know to do that when I change the data validation selection?

Alternatively, I could just send you the file. Is a very small, but nifty expense report.

Lawrence

 

[JoeMo]

Joe,

How do I set a 'break' point in a line of code? <sorry for="" the="" stupid="" questions?="">

As for step through, can I let the macro know to do that when I change the data validation selection?

Alternatively, I could just send you the file. Is a very small, but nifty expense report.

Lawrence

Click in the gray margin to the left of the line:
Application.EnableEvents = False

This should produce a large dot in the margin.
Now go back to your worksheet and press F9 to trigger a calculation (make sure the condition cell is set to True first).
The VBE window will open with the break line highlighted. Now just press the F8 key to step through the rest of the code and observe what's happening, paying particular attention to where/when the 2nd appearance of the message box occurs.

</sorry>

 

[Skysurfer]

Joe,

I did the break procedure and something VERY interesting happened. I created the break, switched back to the spreadsheet and changed the data validation selection so the result went from FALSE to TRUE. The macro ran (via F8) twice. Exactly what I've been experiencing.

Then, I did it again. This time, making a data validation selection that changed the result from TRUE back to FALSE...and the macro again ran twice.

So, I'm thinking about doing something very barbaric and simple to solve this. Since I now know the macro will run twice no matter the TRUE/FALSE state of the test cell, I could add a kill switch for the second run that ends the macro. That is, have the macro place a value in a cell when the macro starts. Say the number 1. And when the macro runs a second time and sees that value--knowing that that it's already run once--it will not not execute the msgbox and instead put a 0 in the kill switch cell and exit the macro.

Does that make sense? Or am I over-complicating this.

Lawrence

 

[JoeMo]

Joe,

I did the break procedure and something VERY interesting happened. I created the break, switched back to the spreadsheet and changed the data validation selection so the result went from FALSE to TRUE. The macro ran (via F8) twice. Exactly what I've been experiencing.

Then, I did it again. This time, making a data validation selection that changed the result from TRUE back to FALSE...and the macro again ran twice.

At what line did the sheet calculate a second time to trigger a second run? When the trigger read False and the macro "ran twice" I presume there was no message box either time - correct?

 

[Skysurfer]

After the first pass through--in the scenario when changing the trigger cell from FALSE to TRUE and triggering the msgbox--the Application.EnableEvents = False line became highlighted after the End Sub and the second pass through started. And because the trigger cell was still TRUE, the msgbox fired again.

However, in the scenario when I used the data validation to change the trigger from TRUE to FALSE, the macro still fired twice...both times NOT triggering the msgbox. That is, the logic worked perfectly in both scenarios, only twice.

There seems to be something strange about putting data validation in table. If I convert the table to a range, the macro only fires once. If I remove the data validation from the cell in the table, the macro fires only once. I'd hoped that each triggers a recalc or an event, but I shut both of those down in the macro.

I'm stumped. Maybe we stumbled on a bug??? I could send you the file to play with.

Thanks again for your time!

Lawrence

 

[JoeMo]

After the first pass through--in the scenario when changing the trigger cell from FALSE to TRUE and triggering the msgbox--the Application.EnableEvents = False line became highlighted after the End Sub and the second pass through started. And because the trigger cell was still TRUE, the msgbox fired again.

However, in the scenario when I used the data validation to change the trigger from TRUE to FALSE, the macro still fired twice...both times NOT triggering the msgbox. That is, the logic worked perfectly in both scenarios, only twice.

There seems to be something strange about putting data validation in table. If I convert the table to a range, the macro only fires once. If I remove the data validation from the cell in the table, the macro fires only once. I'd hoped that each triggers a recalc or an event, but I shut both of those down in the macro.

I'm stumped. Maybe we stumbled on a bug??? I could send you the file to play with.

Thanks again for your time!

Lawrence

That's the first I've heard that the validation cell is in an Excel Table.
I think turning calc off and then back on forces a recalc of tables.

Your double appearance of the message box problem should disappear if you simply comment out or remove the line that changes calc to manual. For that matter, you can remove both Application.Calculation lines and you should be fine with the validation cell remaining in a table.