If cell in column A = "ABC" and cell in column B is blank, then warning message

[jwb1012]

Hello, I am trying to come up with some logic that will search for the text "Task Description" or "Method of Quoting" in column A, and if the cell in column B is blank, then it will display a warning message box.

I expected it to be fairly easy but I cant seem to get there. Any thoughts?

 

[JoeMo]

Hello, I am trying to come up with some logic that will search for the text "Task Description" or "Method of Quoting" in column A, and if the cell in column B is blank, then it will display a warning message box.

I expected it to be fairly easy but I cant seem to get there. Any thoughts?

Use a change event macro on col A so when the text you cite is entered in col A a message box will launch if the companion cell in col B is blank.

 

[jwb1012]

Hmm okay I have googled a bunch, but haven't found too many great examples. Could you possibly help me get started so I can see the structure that you would use if you were setting this up?

 

[BQardi]

You can put this in an ordinary module (f.ex. Module1) and call it (ALT+F8) or create a button and assign the macro to it:

Sub ShowMissingTextWarning()
Dim FndCell As Range, arr As Variant
On Error Resume Next
For Each arr In Array("Task Description", "Method of Quoting")
    Set FndCell = Range("A:A").Find(arr, , xlValues, xlWhole).Offset(0, 1)
    If FndCell.Value = vbNullString Then
        If Err.Number = 0 Then
            MsgBox arr & " is missing!", vbExclamation
            FndCell.Select
        End If
    End If
Next arr
On Error GoTo 0
End Sub

Or as JoeMo suggests, call it from the change event (probably a bit different code he had in mind, though):

Private Sub Worksheet_Change(ByVal Target As Range)
Module1.ShowMissingTextWarning
End Sub

 

[jwb1012]

Okay, so this code seems to work where all cells that say "Task Description:" or "Method of Quoting:" and the cells in column E are blank, it gives two warnings that there are missing task descriptions and method of quotings. But...when only one of them is blank it doesn't catch it. I'm thinking it just finds the first occurrence. and checks that one. ut, I have many occurrence's of the words "Task Description:" or "Method of Quoting:" so I need to make sure that none of these cells in one column over are blank.

Also, it would be AMAZING if I could put the cell addresses of the blank cells in the msg box at the end.

Sub ShowMissingTextWarning()
'Definitions
Dim FndCell As Range, arr As Variant
On Error Resume Next
For Each arr In Array("Task Description:", "Method of Quoting:")
    Set FndCell = Range("D:D").Find(arr, , xlValues, xlWhole).Offset(0, 1)
    If FndCell.Value = vbNullString Then
        If Err.Number = 0 Then
            MsgBox arr & " is missing!", vbExclamation
            FndCell.Select
        End If
    End If
Next arr
On Error GoTo 0
End Sub

 

[BQardi]

Yeah my idea for the code was to find the first occurence of either value and popup the msgbox, then selecting the cell with missing text so you could edit (added the cell-address to the msgbox), then you must run the code again to check for more occurences, and only when no more msgboxes show, then you are safe (no more blanks).
But I made a small mistake; it was not supposed to show two msgboxes when both where missing, to fix this, insert "Exit For" as shown:

Sub ShowMissingTextWarning()
'Definitions
Dim FndCell As Range, arr As Variant
On Error Resume Next
For Each arr In Array("Task Description:", "Method of Quoting:")
    Set FndCell = Range("D:D").Find(arr, , xlValues, xlWhole).Offset(0, 1)
    If FndCell.Value = vbNullString Then
        If Err.Number = 0 Then
            MsgBox arr & " is missing[COLOR=#0000ff] in cell " & FndCell.Address & "[/COLOR]!", vbExclamation
            FndCell.Select
            [COLOR=#ff0000]Exit For[/COLOR]
        End If
    End If
Next arr
On Error GoTo 0
End Sub

We can make one msgbox with addresses of all blanks, but it would require some changes.
Is it something like this you want?

You could also consider using conditional formatting to highlight the blanks instead, no need for VBA for this approach:

Select E:E and insert this formula in conditional formatting, and choose formatting, f.ex. cell fill-color red:

=AND($E1="",OR($D1="Task description:",$D1="Method of Quoting:"))

 

[BQardi]

So I guess this was what you where looking for:

We can make one msgbox with addresses of all blanks, but it would require some changes.
Is it something like this you want?

How about doing it with filtering?

Sub ShowMissingTextWarning()
Dim LastCell As Range
Application.ScreenUpdating = False
Set LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)
With Rows(1)
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="Task description:", Operator:=xlOr, Criteria2:="Method of Quoting:"
    .AutoFilter Field:=2, Criteria1:=""
    MsgBox "These addresses/ranges need attention: " & Range("B2", LastCell).SpecialCells(xlCellTypeVisible).Address
    .AutoFilter
End With
End Sub

 

[jwb1012]

Hi BQardi - thank you very much! I am currently using conditional formatting, but the goal is now to prevent them from being able to export unless these required fields are filled out.

So, the user would hit the macro button below to export to word or pdf. If these cells are not filled in, then it would "exit sub"/not complete the export. It would be great if we could modify the code so that it creates a list of all empty cells.

[

Sub EXportToWord()
    Dim objWord As Word.Application
    Range("Print_Area").Copy
    Set objWord = CreateObject("Word.Application.14")
    With objWord
        .Documents.Add
        .Visible = True
        .Selection.Paste
    End With
End Sub



/CODE]

 

[BQardi]

Try this:

Sub EXportToWord()
Dim objWord As Word.Application
[COLOR=#ff0000]If MissingText Then Exit Sub[/COLOR]
Range("Print_Area").Copy
Set objWord = CreateObject("Word.Application.14")
With objWord
.Documents.Add
.Visible = True
.Selection.Paste
End With
End Sub


Function MissingText() As Boolean
Dim LastCell As Range
MissingText = True
With Rows(1)
    If ActiveSheet.AutoFilterMode = True Then .AutoFilter
    Set LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="Task description:", Operator:=xlOr, Criteria2:="Method of Quoting:"
    .AutoFilter Field:=2, Criteria1:=""
    If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
        .AutoFilter
        MissingText = False
    End If
End With
End Function

 

[jwb1012]

I don't think filtering is what I'm after. I think you were on the right track with the first approach. Is it possible to modify the first approach?