Search code examples
excelvbamsgbox

Find empty cells and print their names in MsgBox

I am working on an archive system where a user lists information about a machine using data validation.

This information gets transferred to a table that looks like this:
| Unit | Machine | PC | Software | Who | Why |

To make the program function, the user must at least insert a machine .

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.
I also want a MsgBox that tells the user what they are missing.

I had two ideas for this.

The program could go through each cell and name it 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 is to consolidate them as a range. The program checks the range, finds the empty cells, and prints it in a MsgBox. I would need to name each cell in some way.

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

I either get object assignment problems or its just, in general, not accepting the IsEmpty part of the program.

Solution

  • Check For Blank Cells

    enter image description here

    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