Find empty cells and print their names in MsgBox

Question

I am working on an archive system where a user lists information about a machine using data validation. This information all gets transferred to a table. The table looks like this:

| Unit | Machine | PC | Software | Who | Why |

In order to even make the program function, the user must insert a machine at least. I want the program to bar the user from logging information if they have not inserted information in either the PC, Software, Who, or Why field. But I also want there to be specifically a MsgBox that tells the user what they are missing.

I had two different ideas for this. I either would have a program that goes through each cell, and then just name it individually in its own MsgBox.

Sub InputCheck()

Dim wbk As Workbook
Dim shtForm As Worksheet

Set wbk = ThisWorkbook
Set shtForm = wbk.Worksheets("Interface")

Dim MachFinder As Range
Dim itm, tbl
Dim listCols As ListColumns


Dim unit_input As String
Dim machine_input As String
Dim pc_inp As String
Dim software_inp As String
Dim who_inp As String
Dim why_inp As String


unit_input = shtForm.Range("A2").Value
machine_input = shtForm.Range("B2").Value
pc_inp = shtForm.Range("C2").Value
software_inp = shtForm.Range("D2").Value
who_inp = shtForm.Range("E2").Value
why_inp = shtForm.Range("F2").Value


Set tbl = randomTable

Set MachFinder = tbl.ListColumns("Machine").DataBodyRange.Find(machine_input, lookat:=xlWhole) 'Find machine name within table
    If Not MachFinder Is Nothing Then
    'Check to see if a PC and software has been inputted
       If IsEmpty(pc_inp) Then
        pcRes = MsgBox("You did not insert the PC for this device." & vbNewLine & vbNewLine & "If you ignore this warning, the machine will no longer be associated with any information, and it will be still be logged. Do you wish to continue?", vbYesNo + vbExclamation + vbDefaultButton2, "Archive System")
            If pcRes = vbNo Then
                Exit Sub
            End If
        End If
        
        If IsEmpty(software_inp) Then
            swRes = MsgBox("You did not insert the software associated with this device." & vbNewLine & vbNewLine & "If you ignore this warning, the machine will no longer be associated with any information, and it will be still be logged. Do you wish to continue?", vbYesNo + vbExclamation + vbDefaultButton2, "Archive System")
                If swRes = vbNo Then
                    Exit Sub
                End If
        End If
        
    'Check to see if user has inserted "Who" and "Why"
        If IsEmpty(who_inp) Then
            whoRes = MsgBox("You must put WHO worked on the device. Please put all information needed and try again.", vbOKOnly + vbExclamation, "Archive System")
            Exit Sub
        End If
        
        If IsEmpty(why_inp) Then
            whyRes = MsgBox("There must be a reason for WHY there was work done on the machine. Please put all information needed and try again.", vbOKOnly + vbExclamation, "Archive System")
            Exit Sub
    End If

My other idea was that I could consolidate all of them together as a range and maybe make it so that the program checks the range, finds the empty cells, and prints it in a MsgBox. But I would need to name each individual cell in some way, and I don't know the best method to do that.

Dim inputs As Range
Dim inpite

Set inputs = shtForm.Range("C2:F2")

        For Each inpite In inputs
        If IsEmpty(inpite.Value) Or inpite.Value = vbNullString Then
        ' I thought I properly named all of the cells within native Excel. It should reference the name of the cell. For instance, C2 is named "ComputerName"
        MsgBox "You are missing an entry for" & inputs.Names.Name & vbNewLine & "Please insert all correct information.", vbOKOnly, "Archive System"
            Exit Sub
        End If
        Next

    'blah blah blah, rest of program...
    
    End If

Whenever I try running it, I either get object assignment problems or its just, in general, not accepting of the "IsEmpty" part of the program. I think that second program could definitely work, I'm just missing something I feel? I want to make this as clean as possible. Thank you for any help!

Range doesn't have `Names`. only `Application, Workbook, Worksheet` have. And `Names` is a collection. — Black cat, Aug 14 '23 at 18:29
Ah, could I make a collection then using `Names.AddName`, and then do `Refers To` for a **specific cell only**? — heiavieh, Aug 14 '23 at 18:37
@TimWilliams Only thing is, whenever I run the code, it doesn't even register the command, it seems. It executes the rest of my program and ignores this part. I don't know if this is because the 'IsEmpty' function doesn't apply in the way I want it to. After the program runs, it runs a `.ClearContents` on that cell range. Do those cells become empty after a `ClearContents` gets ran? — heiavieh, Aug 14 '23 at 19:11
Don't use `IsEmpty` but instead check the length - eg `If Len(pc_inp)=0 Then` as shown below — Tim Williams, Aug 14 '23 at 19:58

VBasic2008 · Accepted Answer · 2023-08-14T19:57:25.873

Check For Blank Cells

Main

Sub YourProcedure()

    If Not IsFormInputValid Then Exit Sub
    
    MsgBox "Form input is valid. Continuing...", vbInformation
    
End Sub

Help

Function IsFormInputValid() As Boolean
    
    Const PROC_TITLE As String = "Form Input Validation"
    
    Dim InputNames(): InputNames = VBA.Array("PC", "Software", "WHO", "WHY")
    
    Dim irg As Range:
    Set irg = ThisWorkbook.Worksheets("Interface").Range("C2:F2")
    
    Dim cell As Range, c As Long, n As Long, MsgString As String
    
    For Each cell In irg.Cells
        If Len(CStr(cell.Value)) = 0 Then
            n = n + 1
            MsgString = MsgString & vbLf & n & ".) " & InputNames(c) _
                & " in cell " & cell.Address(0, 0) & ","
        End If
        c = c + 1
    Next cell
    
    If n = 0 Then
        IsFormInputValid = True
    Else
        MsgString = "You are missing the following entr" _
            & IIf(n = 1, "y", "ies") & ": " & vbLf _
            & Left(MsgString, Len(MsgString) - 1) & "." & vbLf & vbLf _
            & "Please enter the required information."
        MsgBox MsgString, vbCritical, PROC_TITLE
    End If
  
End Function

score 0 · Answer 2 · answered Aug 14 '23 at 18:38

Checking blank cell with Len(Trim(inputs.Cells(i))), then concate column header as popup message.

Sub demo()
    Dim inputs As Range
    Dim inpite, sMissing As String
    Set shtForm = ActiveSheet ' update sheet object as needed
    Set inputs = shtForm.Range("A2:F2")
    For i = 2 To 6
        If Len(Trim(inputs.Cells(i))) = 0 Then
            sMissing = sMissing & "," & _
                inputs.Offset(-1, 0).Cells(i).Value
        End If
    Next
    MsgBox "You are missing entries: " & Mid(sMissing, 2) & _
        vbNewLine & "Please insert all correct information.", _
        vbOKOnly, "Archive System"
    Exit Sub
    ' your code
End Sub

Find empty cells and print their names in MsgBox

2 Answers2

Check For Blank Cells