error checking streamlined

[vttech]

I created my first VBA application and I added error checking for severval text boxes (.txtOrganization, txtStartYear, txtStartMonth, txtEndYear, txtEndMonth) as you can notice the error checking happens in several user define functions and thus if a person does not fill in a text box in several areas. Each function will create a msgbox which can lead to several msgboxes on the screen. How can I streamline this process that only one msgbox appears on the screen stating all the boxes that need to be filled in??? also since this is my first VBA app any ideas how to make this a better app?? Thanks in advance

Private Sub cmdInsert_Click()

    Info
    StartDate
    EndDate
    FTE
    RelevanceScale
End Sub

Dim CellPosition As Range
Dim counter As Integer


Function Info()

counter = 1

Set CellPosition = Range("a13")
    Do While counter <= 10
        If CellPosition.Value = "" Then
            If frmWorkHistory.txtOrganization.Value = "" Then
            MsgBox "Organization Name Field Required"
            Exit Do
            Else
            CellPosition.Value = frmWorkHistory.txtOrganization.Value & " " & frmWorkHistory.txtPosition.Value
            Exit Do
            End If
        Else
        Set CellPosition = CellPosition.Offset(1, 0)
        counter = counter + 1
        End If
    Loop
End Function

Function StartDate()
counter = 1
Set CellPosition = Range("C13")
    Do While counter <= 10
        If CellPosition.Value = "" Then
            If frmWorkHistory.txtStartYear.Value = "" Or frmWorkHistory.txtStartMonth.Value = "" Then
            MsgBox = "Start Date Field Required"
            Exit Do
            Else
            CellPosition.Value = DateSerial(frmWorkHistory.txtStartYear.Value, frmWorkHistory.txtStartMonth.Value, 1)
            Exit Do
            End If
        Else
        Set CellPosition = CellPosition.Offset(1, 0)
        counter = counter + 1
        End If
    Loop
End Function

Function EndDate()
counter = 1
Set CellPosition = Range("D13")
    Do While counter <= 10
        If CellPosition.Value = "" Then
 If frmWorkHistory.txtEndYear.Value = "" Or frmWorkHistory.txtEndMonth.Value = "" Then
            MsgBox = "End Date Field Required"
            Exit Do
            Else
            CellPosition.Value = DateSerial(frmWorkHistory.txtEndYear.Value, frmWorkHistory.txtEndMonth.Value, 1)
            Exit Do
            End If
        Else
        Set CellPosition = CellPosition.Offset(1, 0)
        counter = counter + 1
        End If
    Loop
End Function


Function FTE()
counter = 1
Set CellPosition = Range("F13")
    Do While counter <= 10
        If CellPosition.Value = "" Then
        CellPosition.Value = frmWorkHistory.cboFTE.Value
        Exit Do
        Else
        Set CellPosition = CellPosition.Offset(1, 0)
        counter = counter + 1
        End If
    Loop
End Function

Function RelevanceScale()
counter = 1
Set CellPosition = Range("G13")
    Do While counter <= 10
        If CellPosition.Value = "" Then
        CellPosition.Value = frmWorkHistory.cboRelevanceScale.Value
        Exit Do
        Else
        Set CellPosition = CellPosition.Offset(1, 0)
        counter = counter + 1
        End If
    Loop
End Function

 

[fumei]

I am sure other will come up with a better way, but you could make an array of your textboxes, and an array of whether it is blank or not.

Private Sub CommandButton1_Click()
Dim Blank(4) As Boolean
Dim Message(4) As String
Dim var, var2
Dim sControlName As String
Dim msg As String

Message(0) = " Organization "
Message(1) = " Start Year "
Message(2) = " StartMonth "
Message(3) = " End Year "
Message(4) = " End Month "

For var = 0 To 4 ' number of textboxes
  sControlName = "Textbox" & var + 1
  If Me.Controls.Item(sControlName).Value = "" Then
    Blank(var) = True
  Else
    Blank(var) = False
  End If
Next

For var2 = 0 To UBound(Blank)
 If Blank(var2) = True Then
    msg = msg & Message(var2) & "is blank.  This is a " _
      & "required field." & vbCrLf & vbCrLf
 End If
Next
MsgBox msg
End Sub

This build the array of True or False for each textbox, then if True (it is blank) build the message. You would need another array as I was using textbox + number. The array would be made of the actual names of your textboxes.

Like I said there are better ways. Just throwing this out.

Gerry

 

[Bong]

Well, basically the same thing but a little lighter:
1. Have a flag, initially 0
2. Any of your text box errors
a) sets the flag to 1
b) appends the message to a string (with a LF)
3. At the end of the checks, if the flag is 1, use the full message string in a message box.

_________________
Bob Rashkin

 

[fumei]

The problem is with "Any of your text box errors".

What if textbox A is "", textbox B is "blah blah, textbox C is "BlahbBbbbb", and textbox D is ""? You need logic to append the message for A, NOT B, NOT C, and for D.

Gerry