Workbook close event not working right

[TheWennerWoman]

Hello,

I have this

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("Uploader")
        If .Visible And posted = 0 Then MsgBox ("You haven't uploaded data to the database!"), vbCritical
        Cancel = True
End With
End Sub

"posted" is a public variable that gets flipped to 1 when the data is uploaded to the database correctly.

If I make the worksheet "uploader" very hidden and attempt to close, the code above kicks in when I don't think it should?

I'm guessing my syntax is wrong somewhere?

Thanks as always for any advice!

 

[RoryA]

It's a common mistake: Worksheet.Visible is not a Boolean (it has 3 possible values). Since xlSheetVeryHidden has a value of 2, if you coerce that to a Boolean it will return True as it is non-zero. You should explicitly test if it is visible like this:

If .Visible = xlSheetVisible

 

[dmt32]

Hi
try this update to your code & see if resolves your issue

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    With Worksheets("Uploader")
            If .Visible = xlSheetVisible And Posted = 0 Then
                MsgBox "You haven't uploaded data to the database!", vbCritical, "Attention"
                Cancel = True
            End If
    End With
    
End Sub

Dave

 

[TheWennerWoman]

Thanks both, each solution works perfectly - can only mark one as the answer though.

 

[RoryA]

I'm going to mark Dave's solution as the answer as it also addresses the other issue that your Cancel = True would always be executed with your original version as it wasn't part of the If block.