VBA If Statement Evaluating After True

CokeOrCrack

Board Regular
Joined
Dec 13, 2015
Messages
77
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. 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
77
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

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,141,316
Messages
5,705,708
Members
421,406
Latest member
kluna90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top