VBA If Statement Evaluating After True

CokeOrCrack

Board Regular
Joined
Dec 13, 2015
Messages
75
Hello:

My workbook has 2 sheets, "Numbers" and "General".

I have an If statement that runs at workbook close.

Code:
    If Sheets("Numbers").Range("BA2") = 0 Then        Sheets("Numbers").Range("BA3") = "Correct"
    ElseIf Sheets("Numbers").Range("BA2") <> 0 Then
        Sheets("General").Range("A13").ClearContents
    End If
When BA2 in sheet "Numbers" is 0, BA3 shows "Correct".

The issue is that I have a Worksheet_Change sub in the "General" sheet. This sub updates a timestamp in that sheet when it is in any way changed.

After running the If statement at workbook close, the timestamp is still updating, even when BA2 = 0.

I have tried removing the above If statement, and the timestamp does not update at close without it, which confirms that the above If statement is what is causing the timestamp to update.


Question:

Is there a way to end an If statement once it is true, but continue to evaluate if it is initially false?

Thanks

OJ
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
The Part after the Then should be on the next line, but there is no need for ElseIf, because it is either 0 or it is not.
If Then Else should suffice:

Code:
Sub test()
If Sheets("Numbers").Range("BA2") = 0 Then
    Sheets("Numbers").Range("BA3") = "Correct"
Else
    Sheets("General").Range("A13").ClearContents
End If
End Sub
 
Last edited:

CokeOrCrack

Board Regular
Joined
Dec 13, 2015
Messages
75
Scott:

Thank you for looking into this. I forgot the "General" sheet had a Pivot Table in it to the far right that I needed to scroll to in order to see.

I moved the Pivot Table to the "Numbers" sheet and it now works perfect.

Thanks

OJ
 

Crystalyzer

Board Regular
Joined
Oct 18, 2011
Messages
177
So basically if the first IF statement in the Workbook_Close event is true, you want to update the worksheet without invoking the Worksheet_Change event, is that correct?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,375
Messages
5,468,238
Members
406,575
Latest member
Joe00

This Week's Hot Topics

Top