Check my Syntax

[PaulFogel123]

I have a line of code in my workbook that distinguishes between two different conditions:

If Range("TrueMonth3") = True And Range("Month3Total") > 0 Then

If Range("TrueMonth3") = True And Range("Month3Total") = 0 Then

where TrueMonth3 and Month3 are named cells. I'm trying to duplicate this formula in VBA:
AND(TrueMonth3,Month3Total>0), AND(TrueMonth3,Month3Total=0).
Yet the macro executes the code that follows as if these two statements aren't even there!

I can't send the file because it's huge. Any ideas?

 

[vletm]

PaulFogel123
Create a new Excel-file, which has needed sample datas , sheets with sample data, macros and so on.

 

[KidneyStone]

Where is the code located?, if it is in a module you may have to reference the worksheet: sheet1.range("M3T") etc, if it is on the sheet code is in linked to a worksheet change event ? if so what is the syntax you have used there?

More context is needed.

 

[PaulFogel123]

Where is the code located?, if it is in a module you may have to reference the worksheet: sheet1.range("M3T") etc, if it is on the sheet code is in linked to a worksheet change event ? if so what is the syntax you have used there?

More context is needed.

That's an idea. Let me reference the worksheet, not just the range name, and see what happens.

 

[PaulFogel123]

Didn't have any effect. Here's the full code (I've simplified it for space):

>>> use code - tags <<<

Sub CheckBoxFYMonth4_Click()
'
' CheckBoxFYMonth4_Click Macro
'
If Range("TrueMonth4") And Range("Month4Total") > 0 Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Sheets("Assumptions").Select
ActiveSheet.Unprotect Password:="EIS 2017"
Application.Calculation = xlManual
    
    ThisWorkbook.Worksheets("Assumptions").Range("MDCMixMonth4copy").Copy
    ThisWorkbook.Worksheets("Assumptions").Range("MDCMixMonth4paste").PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
    
    ThisWorkbook.Worksheets("Assumptions").Range("MDCMixMonth4Acopy").Copy
    ThisWorkbook.Worksheets("Assumptions").Range("MDCMixMonth4Apaste").PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
  
    Sheets("Assumptions").Select
    ActiveSheet.Protect Password:="EIS 2017"
    Sheets("Settings").Select
    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    MsgBox "Current month successfully loaded"
    Application.Calculation = xlAutomatic

  End If
      
If Range("TrueMonth4") And Range("Month4Total") = 0 Then
    
    MsgBox "Actual data for this month must be loaded first", vbCritical
        With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With
        Range("TrueMonth4") = False
        Sheets("Assumptions").Select
        ThisWorkbook.Worksheets("Assumptions").Range("ProductivityMonth4paste").Value = ThisWorkbook.Worksheets("Assumptions").Range("ProductivityMonth4copy").Value
        Sheets("Settings").Select
        With Application
        .EnableEvents = True
        .ScreenUpdating = True
        End With
        End If
End Sub

This is a checkbox-activated macro. Clicking the checkbox changes the linked cell to TRUE, and then the first IF statement compares that TRUE with whether there is data in month 4. If there is, it executes a series of copy/paste commands and ends with the message, "Current month successfully loaded". If there's no data (Range("Month4Total") = 0 ), the second IF statement sends a message (MsgBox "Actual data for this month must be loaded first") and then resets a few things.

However, in both conditions, the message box never appears. In both conditions, the code for the first condition executes regardless of which condition is true.

I thought maybe the order of the code is wrong but I can't figure it out.

Paul

 

[KidneyStone]

Have you checked what is being returned by the values you are relying on? eg:

>>> use code - tags <<<

debug.print  Range("TrueMonth4")

inserted before the if statement, sometimes the result is unexpected as the sheet does not always hold a boolean but returns a 0, -1 or other value for a true (I have this on one of my projects but I have not got around to working it out properly yet, I am just working around it)
apart from that I highly recommend getting away from any .select use unless there is a reason you need to, any manipulation can be performed on a sheet without selecting it.
Also in your code, use the following syntax:

dim ws1 as workshet: set ws1 = ThisWorkbook.Worksheets("Assumptions")

then:

with ws1
.range(etc)
.protect password... etc
end with

this saves rewriting a lot of code all the time.

If you cannot load an example worksheet it is difficult to identify what the problem is because there are a number of points at which it could be breaking, for good practice though ensure you have "option explicit" at the top of the page and use debug.print to ensure your variables are returning the expected values. debug.print ws1.name should return the name of the sheet for example.