CODEWITHSUNDEEP
vbaexcel
Diego Trec
Diego Trec

Reputation: 5

VBA 2 IF conditions applied for column

this is my first post, please be patient if I'm doing/asking something wrong. My issue is: I got 2 columns, A is number of children, B is name of those children. Those values are manually entered, I simply would like to have B mandatory if A is filled. Here is what I thought:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Not IsEmpty(Sheet1.Range("A1")) Then
            If IsEmpty(Sheet1.Range("B1")) Then
                MsgBox "Please fill in cell B1 before closing."
                Cancel = True
            Else '
            End If
        End If
    End Sub

This is actually working perfectly, unfortunately I can't manage to extend it for whole columns, when replacing A1 with A1:A1000 and B1 with B1:B1000 for instance,it doesn't work.

How can I validate this for both entire column A and B?

thanks in advance!

Upvotes: 0

Views: 70

Answers (2)

A.S.H
A.S.H

Reputation: 29332

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = Evaluate("SUMPRODUCT(--(ISBLANK(Sheet1!B:B) <> ISBLANK(Sheet1!A:A)))")
    If Cancel Then MsgBox "Please fill in column B before closing."
End Sub

EDIT

In order to take the user to the place where data is missing, and taking into account the additional information you provided about your data, try this:

'Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim r: r = Evaluate( _
      "MATCH(FALSE, ISBLANK('ELENCO AGGIORNATO'!V:V) = ISBLANK('ELENCO AGGIORNATO'!W:W), 0)")
    If IsError(r) Then Exit Sub ' All is fine
    Cancel = True
    Application.Goto Sheets("ELENCO AGGIORNATO").Cells(r, "V").Resize(, 2)
    msgBox "Please fill missing data before saving."
End Sub

Also note that I recommend Workbook_BeforeSave instead of Workbook_BeforeClose, because there's no harm if the user decides to drop his (incomplete) work and close the workbook without saving.

Upvotes: 2

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You may try something like this...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim str As String
    Dim Rng As Range, Cell As Range
    Dim FoundBlank As Boolean
    Set Rng = Sheet1.Range("A1:A1000")
    str = "Please fill the cells listed below before colsing..." & vbNewLine & vbNewLine
    For Each Cell In Rng
        If Cell <> "" And Cell.Offset(0, 1) = "" Then
            FoundBlank = True
            str = str & Cell.Address(0, 1) & vbNewLine
        End If
    Next Cell
    If FoundBlank Then
        Cancel = True
        MsgBox str, vbExclamation, "List of Blank Cells Found!"
    End If
End Sub

Upvotes: 0

Related Questions