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?

 

[jwb1012]

Would it be possible to just say "Please check the WBS Description, Task Description, and/or Method of Quoting in the following cells: "

"First Cell that meets criteria"
"Second Cell that meets criteria"

 

[BQardi]

Well, it meets all of your criterias.
It works like this (if used correctly):
When you run "EXportToWord" the MissingText function will run before allowing the rest of your code to be executed.
It will filter all your data, and if blanks are found, leave the filter as is with only those blank lines visible and will Exit Sub, allowing you to correct/edit. With this of course making sure you are not allowed to continue the word function.
When corrected you must run your word function again, and if no more blanks are found, the filtered list will "unfilter" and allow you to continue with the rest of your word function.

I am absolutely positive that a non filtering approach would work. Unfortunately time is not on my side these days.

Didn't any of the other posters suggestions work?

 

[jwb1012]

Okay, so I was able to modify the code and eliminate the feature that listed all empty cells in the error msg box, which makes this easier.

Now, I have combined it with my export to pdf code. But, it continues to export to pdf over and over and over again. I must be stuck in a loop. Any thoughts?

If the user hits the button and all required fields are filled in, then it should export to pdf 1 time. If there are missing required fields, then it should display the warning message and exit sub.

Also, is it possible to bring up the "Save As" window instead of auto-saving this document by the worksheet name?

Sub ErrorCatch2()
'Definitions
Dim FndCell As Range, arr As Variant
On Error Resume Next
    For Each arr In Array("WBS Description:", "Task Description:", "Method of Quoting:", "Source of Data:")
    Set FndCell = Range("D:D").Find(arr, , xlValues, xlWhole).Offset(0, 1)
    If FndCell.Value = vbNullString Then
        If Err.Number = 0 Then
            MsgBox "Please verify that all required fields have been completed." & Chr(10) & Chr(10) & "The fields required to export the BOEs are" & Chr(10) & Chr(10) & "    - WBS Description" & Chr(10) & Chr(10) & "    - Task Description" & Chr(10) & Chr(10) & "    - Method of Quoting" & Chr(10) & Chr(10) & "    - Source of Data"
            FndCell.Select
            Exit For
        End If
        
    Else
    
    pdfName = ActiveSheet.Name
    ChDir ActiveWorkbook.Path & "\"
    fileSaveName = ActiveSheet.Name
    
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    
    MsgBox "File Saved: " & " " & fileSaveName
    End If
    
Next arr
On Error GoTo 0
End Sub

 

[BQardi]

Something like this?

Sub ErrorCatch2()
Dim wbPath As String, pdfName As String
Dim FndCell As Range, arr As Variant
On Error Resume Next
For Each arr In Array("WBS Description:", "Task Description:", "Method of Quoting:", "Source of Data:")
    Set FndCell = Range("D:D").Find(arr, , xlValues, xlWhole).Offset(0, 1)
    If FndCell.Value = vbNullString Then
        If Err.Number = 0 Then
            MsgBox "Please verify that all required fields have been completed." & Chr(10) & Chr(10) & "The fields required to export the BOEs are" & Chr(10) & Chr(10) & "    - WBS Description" & Chr(10) & Chr(10) & "    - Task Description" & Chr(10) & Chr(10) & "    - Method of Quoting" & Chr(10) & Chr(10) & "    - Source of Data"
            FndCell.Select
            Exit Sub
        End If
    End If
Next arr
On Error GoTo 0
pdfName = ActiveSheet.Name
wbPath = ActiveWorkbook.Path
If Not Right(wbPath, 1) = "\" Then wbPath = wbPath & "\"
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .InitialFileName = wbPath
    .Title = "Select or Enter SaveAs Filename"
    .Filters.Clear
    .Filters.Add "PDF", "*.pdf"
    If Not .Show = -1 Then Exit Sub
    wbPath = .SelectedItems(1)
End With
If Not Right(wbPath, 1) = "\" Then wbPath = wbPath & "\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        wbPath & pdfName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub