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.
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