Validating Entries VBA

[cmxulb]

Hi All,
Currently, no field can be empty, when I hit save. My question is, how can I make it so that when Primed is selected and any other fields are empty it would auto writes N/A on the database when I click save?

Function ValidateEntries() As Boolean

ValidateEntries = True

Dim iMaterialID As Variant

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Print")

iMaterialID = frmForm.txtMaterialID.Value

With frmForm

'Default Color

.txtMaterialID.BackColor = vbWhite
.cmbValve.BackColor = vbWhite
.txtintfineflow.BackColor = vbWhite
.txtintcoarseflow.BackColor = vbWhite
.txtFineFlow.BackColor = vbWhite
.txtCoarseFLow.BackColor = vbWhite
.txtComments.BackColor = vbWhite
'--------------------------------

If Trim(.txtMaterialID.Value) = "" Then

MsgBox "Please enter Material ID.", vbOKOnly + vbInformation, "Material ID"
ValidateEntries = False
.txtMaterialID.BackColor = vbRed
.txtMaterialID.SetFocus
Exit Function

End If

'Validating Duplicate Entries

If Not sh.Range("B:B").Find(what:=iMaterialID, lookat:=xlWhole) Is Nothing Then

MsgBox "Duplicate Material ID found.", vbOKOnly + vbInformation, "Material ID"
ValidateEntries = False
.txtMaterialID.BackColor = vbRed
.txtMaterialID.SetFocus
Exit Function

End If

If Trim(.txtComments.Value) = "" Then

MsgBox "Please enter Comments.", vbOKOnly + vbInformation, "Comments"
ValidateEntries = False
.txtComments.BackColor = vbRed
.txtComments.SetFocus
Exit Function

End If


'Validating Scale


If .optLarge.Value = False And .optSmall.Value = False And .optBoth.Value = False Then

MsgBox "Please select Scale.", vbOKOnly + vbInformation, "Scale"
ValidateEntries = False
Exit Function

End If


If .CheckYes.Value = False And .CheckNo.Value = False Then

MsgBox "Please select Locked Yes or No.", vbOKOnly + vbInformation, "Locked"
ValidateEntries = False
Exit Function

End If


If .CheckYess.Value = False And .CheckNoo.Value = False Then

MsgBox "Please select Locked Yes or No.", vbOKOnly + vbInformation, "Locked"
ValidateEntries = False
Exit Function

End If



If Trim(.cmbValve.Value) = "" Then

MsgBox "Please select Valve from drop-down.", vbOKOnly + vbInformation, "Valve"
ValidateEntries = False
.cmbValve.BackColor = vbRed
.cmbValve.SetFocus
Exit Function

End If


If Trim(.txtFineFlow.Value) = "" Then

MsgBox "Please enter Fine Flow.", vbOKOnly + vbInformation, "Fine Flow"
ValidateEntries = False
.txtFineFlow.BackColor = vbRed
.txtFineFlow.SetFocus
Exit Function

End If

If Trim(.txtCoarseFLow.Value) = "" Then

MsgBox "Please enter Coarse Flow.", vbOKOnly + vbInformation, "Coarse Flow"
ValidateEntries = False
.txtCoarseFLow.BackColor = vbRed
.txtCoarseFLow.SetFocus
Exit Function

End If


If Trim(.txtintcoarseflow.Value) = "" Then

MsgBox "Please enter Int Coarse Flow.", vbOKOnly + vbInformation, " Int Coarse Flow"
ValidateEntries = False
.txtintcoarseflow.BackColor = vbRed
.txtintcoarseflow.SetFocus
Exit Function

End If


If Trim(.txtintfineflow.Value) = "" Then

MsgBox "Please enter Int Fine Flow.", vbOKOnly + vbInformation, "Int Fine Flow"
ValidateEntries = False
.txtintfineflow.BackColor = vbRed
.txtintfineflow.SetFocus
Exit Function

End If
End With



End Function

 

[rlv01]

Add code to fill empty textboxes.

If chkPrimeYes.Value = True Then ' "Yes" box is checked
    SetEmptyTextBoxesToNA
End If

End Sub

Private Sub SetEmptyTextBoxesToNA()
    Dim Ctrl As Control
   
    For Each Ctrl In Me.Controls    ' "Me" references the UserForm
        If TypeName(Ctrl) = "TextBox" Then
            If Trim(Ctrl.Text) = "" Then
                Ctrl.Text = "N/A"
            End If
        End If
    Next Ctrl
End Sub

ps. Please try to use code tags when posting code.

 

[cmxulb]

Add code to fill empty textboxes.

If chkPrimeYes.Value = True Then ' "Yes" box is checked
    SetEmptyTextBoxesToNA
End If

End Sub

Private Sub SetEmptyTextBoxesToNA()
    Dim Ctrl As Control
  
    For Each Ctrl In Me.Controls    ' "Me" references the UserForm
        If TypeName(Ctrl) = "TextBox" Then
            If Trim(Ctrl.Text) = "" Then
                Ctrl.Text = "N/A"
            End If
        End If
    Next Ctrl
End Sub

ps. Please try to use code tags when posting code.

Add code to fill empty textboxes.

If chkPrimeYes.Value = True Then ' "Yes" box is checked
    SetEmptyTextBoxesToNA
End If

End Sub

Private Sub SetEmptyTextBoxesToNA()
    Dim Ctrl As Control
  
    For Each Ctrl In Me.Controls    ' "Me" references the UserForm
        If TypeName(Ctrl) = "TextBox" Then
            If Trim(Ctrl.Text) = "" Then
                Ctrl.Text = "N/A"
            End If
        End If
    Next Ctrl
End Sub

ps. Please try to use code tags when posting code.

Where do I put add those codes? and do you have to use both codes?

 

[rlv01]

1. There is a button on your form shown as "Save". I would recommend putting the code checking the status of your Primed "Yes" checkbox in the button click event for the Save button. Example:

Private Sub cmdSave_Click()
    If chkPrimeYes.Value = True Then ' "Yes" box is checked
        SetEmptyTextBoxesToNA
    End If
End Sub

with the subroutine SetEmptyTextBoxesToNA located in the same code module.

Note that cmdSave is the name of MY button control. You will need to use the name of your button control. Similarly chkPrimeYes is the name of my "Yes" Primed checkbox. You will need to change it to your name.

2. Yes both pieces of code are needed.

 

[cmxulb]

1. There is a button on your form shown as "Save". I would recommend putting the code checking the status of your Primed "Yes" checkbox in the button click event for the Save button. Example:

Private Sub cmdSave_Click()
    If chkPrimeYes.Value = True Then ' "Yes" box is checked
        SetEmptyTextBoxesToNA
    End If
End Sub

with the subroutine SetEmptyTextBoxesToNA located in the same code module.

Note that cmdSave is the name of MY button control. You will need to use the name of your button control. Similarly chkPrimeYes is the name of my "Yes" Primed checkbox. You will need to change it to your name.

2. Yes both pieces of code are needed.

Thanks for the reply

it did te N/A but at the end it comes up with this error

 

[cmxulb]

1. There is a button on your form shown as "Save". I would recommend putting the code checking the status of your Primed "Yes" checkbox in the button click event for the Save button. Example:

Private Sub cmdSave_Click()
    If chkPrimeYes.Value = True Then ' "Yes" box is checked
        SetEmptyTextBoxesToNA
    End If
End Sub

with the subroutine SetEmptyTextBoxesToNA located in the same code module.

Note that cmdSave is the name of MY button control. You will need to use the name of your button control. Similarly chkPrimeYes is the name of my "Yes" Primed checkbox. You will need to change it to your name.

2. Yes both pieces of code are needed.

 

[Gokhan Aycan]

It would have thrown an error regardless of the change. iRow is declared as Long, while textboxes hold string values.

Try changing these lines:

If frmForm.txtRowNumber = "" or frmForm.txtRowNumber = "N/A" Then
    iRow = [CountA()Database!A:A] + 1
Else
    iRow = CLng(frmForm.txtRowNumber.Value)
End If

 

[cmxulb]

It would have thrown an error regardless of the change. iRow is declared as Long, while textboxes hold string values.

Try changing these lines:

If frmForm.txtRowNumber = "" or frmForm.txtRowNumber = "N/A" Then
    iRow = [CountA()Database!A:A] + 1
Else
    iRow = CLng(frmForm.txtRowNumber.Value)
End If

The N/A works! thanks... but only for material, comments, int fine flow, int coarse flow, fine flow, and coarse flow.

How would you do it for Scale, Valve Type, and for locked to auto write no

 

[Gokhan Aycan]

Try:

Private Sub SetEmptyTextBoxesToNA()
    Dim Ctrl As Control
   
    For Each Ctrl In Me.Controls    ' "Me" references the UserForm
        If TypeName(Ctrl) = "TextBox" Then
            If Trim(Ctrl.Text) = "" Then
                Ctrl.Text = "N/A"
            End If
        End If
    Next Ctrl
    
    Me.optBoth.Value = True
    Me.cmbValve.Value = "N/A"
    Me.CheckYes.Value = False
    Me.CheckNo.Value = True
    
End Sub

By the way, I noticed you have both CheckYes and CheckYess (Locked and Prime I suppose), I don't know which is which as both has the same message box prompt.

 

[cmxulb]

Try:

Private Sub SetEmptyTextBoxesToNA()
    Dim Ctrl As Control
  
    For Each Ctrl In Me.Controls    ' "Me" references the UserForm
        If TypeName(Ctrl) = "TextBox" Then
            If Trim(Ctrl.Text) = "" Then
                Ctrl.Text = "N/A"
            End If
        End If
    Next Ctrl
   
    Me.optBoth.Value = True
    Me.cmbValve.Value = "N/A"
    Me.CheckYes.Value = False
    Me.CheckNo.Value = True
   
End Sub

By the way, I noticed you have both CheckYes and CheckYess (Locked and Prime I suppose), I don't know which is which as both has the same message box prompt.

Yes! nice thanks man!..Yea CheckYes is "Locked"