BeforeSave Check two ranges cancel if one is empty and the other not

[MoutainManThan]

Hi, I'm fairly new to vba and have been trying to create a sub that when the workbook is saved it checks two different ranges. If either one is empty and the other has a value in it cancel save and display a msg. if both are empty or both have values continue with save. The workbook has a master sheet (which stays very hidden), a summary sheet and then 32 copies of the master sheet. Ideally I want to check all 32 copies. The data in the ranges should just be integers if that make any difference. I've tried Select Case, If Then statements, If And Then statements, and all sorts of random stuff I've found online. I'm not receiving any error messages, but the sub is not preventing me from saving or displaying the msgbox when the right conditions are met.

The Code I have right now is just pieces of different attempts I've made. Some old stuff is commented out. any suggestions or tips welcome.

here's my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Msg As String
Msg = "Wrong Form. Try Again."
Dim i As Integer


If SaveAsUI Then
    For i = 1 To 32
        If IsEmpty(Sheet("PI" & i).Range("I14:J14")) And IsEmpty(Sheet("PI" & i).Range("I17:J17")) = False Then
            MsgBox Msg
            Cancel = True
            Next i
        End If
    
    
    
    'If IsEmpty(Range("I14")) = True And IsEmpty(Range("I17")) = False Then
        'MsgBox Msg
       'Cancel = True
    'End If


    'If IsEmpty(Range("I14")) = False And IsEmtpy(Range("I17")) = True Then
        'MsgBox Msg
        'Cancel = True
    'End If
End If
   


    


End Sub

 

[Fluff]

Hi & welcome to the board
Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim Msg As String
    Msg = "Wrong Form. Try Again."
    Dim i As Integer


    For i = 1 To 32
        With Sheets("PI" & i)
            If WorksheetFunction.CountA(.Range("I14:J14")) <> 2 _
                    Or WorksheetFunction.CountA(.Range("I17:J17")) <> 2 Then
                MsgBox Msg
                Cancel = True
            End If
        End With
    Next i
    
End Sub

 

[MoutainManThan]

Hey, still not working. Thanks for the help tho. whats the meaning behind "<>2_"?

 

[MoutainManThan]

Hey Fluff, still not working. Thanks for the help tho. what's the meaning behind "<>2_"?

 

[Fluff]

Hey, still not working. Thanks for the help tho. whats the meaning behind "<>2_"?

It's basically saying that if the count doesn't = 2.
Are your 2 ranges merged cells?

 

[MoutainManThan]

ahh, i see. yes they are merged cells. the first range to check is the i14:j14 and the second range is the i17:j17 (both merged cells)

 

[MoutainManThan]

I had been trying it with just I14 and I17. thought it might help to add the J's in

 

[Fluff]

In that case try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Msg As String
Msg = "Wrong Form. Try Again."
Dim i As Integer


    For i = 1 To 32
        With Sheets("PI" & i)
            If WorksheetFunction.CountA(.Range("I14:J14")) <> 1 _
                    Or WorksheetFunction.CountA(.Range("I17:J17")) <> 1 Then
                MsgBox Msg
                Cancel = True
                Exit Sub
            End If
        End With
    Next i
    
End Sub

 

[MoutainManThan]

No that didn't work either. I don't think Or will work. I'm trying to get it to Cancel only if one range has a value And the other doesn't. Wouldn't I have to use an And statement?

 

[Fluff]

Give this a go

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim Msg As String
    Msg = "Wrong Form. Try Again."
    Dim i As Integer
    Dim Rng As Range


    For i = 1 To 32
        With Sheets("PI" & i)
            If WorksheetFunction.CountA(Union(.Range("I14"), .Range("I17"))) = 1 Then
                MsgBox Msg
                Cancel = True
                Exit Sub
            End If
        End With
    Next i

End Sub

ps: At the moment this will quit as soon as a sheet is found that is "wrong".
Is this what you want, or would you prefer to check all 32 sheets & then report which of those sheets is wrong?