Data validation UserForm Text Only

[Karmadyl]

Good day,

I am currently trying to validate data using the user form. What I am trying to accomplish is to make mandatory fields filled before the user is able to submit the entered data. I am trying to use something like IsNumeric to validate, but can't find something similar regarding strings

Private Sub OnlyNumbers()If TypeName(Me.ActiveControl) = "TextBox" Then


        With Me.ActiveControl


            If Not IsNumeric(.Value) And .Value <> vbNullString Then


                MsgBox "Sorry, only numerical data may be entered"


                .Value = vbNullString


            End If


        End With


    End If


End Sub

I've tried something like:

Private Sub FreightFowarder_Change()
If FreigtFowarder.Text = "" Then
MsgBox "Please enter a Freight Fowarder"
Exit Sub
End if
End Sub 

But the data was still inserted in my table. I would like that the data doesn't get submitted until all mandatory fields are filled.

What should I do? Here's my command button code :

Private Sub CommandButton1_Click()    
    Dim R As Long
    Dim Rng As Range
    Dim ws As Worksheet


    Set ws = Sheets("OutgoingBagIssues")


    With ws.ListObjects("ExhibitorList")
        Set Rng = .ListColumns(1).Range
        R = Rng.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
        .ListRows(R).Range.Cells(1, 1).Select
        'freight fowarder
        .ListRows(R).Range.Cells(1, 1).Value = Me.FreightFowarder.Value
        'Mission
        .ListRows(R).Range.Cells(1, 2).Value = Me.Mission.Value
        'Classification
        .ListRows(R).Range.Cells(1, 3).Value = Me.Classification.Value
        'Total Bags Shipment
        .ListRows(R).Range.Cells(1, 4).Value = Me.TotalShipment.Value
        'Numbers of Bags Affected
        .ListRows(R).Range.Cells(1, 5).Value = Me.BagsAffected.Value
        'AWBs
        .ListRows(R).Range.Cells(1, 6).Value = Me.AWBs.Value
        'Bag Numbers
        .ListRows(R).Range.Cells(1, 7).Value = Me.BagNumbers.Value
        'Issues
        .ListRows(R).Range.Cells(1, 8).Value = Me.Issues.Value
        'Date
        .ListRows(R).Range.Cells(1, 9).Value = Me.ManualDate.Value
        'Extra notes
        .ListRows(R).Range.Cells(1, 10).Value = Me.ExtraNotes.Value
        'Submitter
        .ListRows(R).Range.Cells(1, 11).Value = Me.Submitter.Value
        'IBP #
        .ListRows(R).Range.Cells(1, 12).Value = Me.InfoBank.Value
'        .ListRows.Add AlwaysInsert:=True
    
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Freight Fowarder: " & Me.FreightFowarder.Value & vbNewLine & _
                "Mission: " & Me.Mission.Value & vbNewLine & _
                "Classification: " & Me.Classification.Value & vbNewLine & _
                "Total Shipment (Bags): " & Me.TotalShipment.Value & vbNewLine & _
                "Number Of Bags Affected: " & Me.BagsAffected.Value & vbNewLine & _
                "Bag Numbers: " & Me.BagNumbers.Value & vbNewLine & _
                "AWBs: " & Me.AWBs.Value & vbNewLine & _
                "Issues: " & Me.Issues.Value & vbNewLine & _
                "Date: " & Me.ManualDate & vbNewLine & _
                "Notes: " & Me.ExtraNotes & vbNewLine & _
                "Submitter: " & Me.Submitter & vbNewLine & _
                "Info Bank Path: " & Me.InfoBank.Value
                  On Error Resume Next
    With xOutMail
        .To = "" 'Email Needs to be added keeping this as a reference
        .CC = ""
        .BCC = ""
        .Subject = "Diplomatic Bag Issues"
        .Body = xMailBody
        .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing




    End With
    Call Userform_Initialize
End Sub

Best regards,

 

[silentwolf]

Hi,

take a sit back and watch this video and you will finde many ideas to validate userforms..

https://www.wiseowl.co.uk/vba-macros/videos/vba-user-forms/validating-forms

HTH

 

[Karmadyl]

Good day SilentWolf, I will surely listen to the above! Thanks

In the meantime, i've figured a way to validate my TextBoxes and ComboBoxes with the following code (pretty sure that is not efficient however) .This way I make sure no numerical data has been entered in my strings only TextBoxes and ComboBoxes (However only at the beginning) :

Private Sub TextOnlyMission()If TypeName(Me.ActiveControl) = "TextBox" Then
    With Me.ActiveControl
        If Mission.Value = IsNumeric(.Value) Then
        MsgBox "No numerical data may be entered, please enter the mission's acronym"
        End If
        End With
        End If
End Sub

And to make sure all mandatory fields are filled, I only had to add Exit Sub in order for my form to not fill my table before all fields are filled :

If Mission.Value = "" Then
        MsgBox "Please enter the mission's acronym"
        Mission.BackColor = RGB(255, 0, 0)
        Exit Sub
        End If

Best regards,