How to hide the userform once the command button has been selected if there were no message boxes opened due to blank field

  • I am very new to programming and this is my first thread ever so i apologies in advance for my ignorance. That being said, I have been working on making a excel document from snipits of code Ive found in different places that, once opened, displays a custom userform with a number of text boxes, combo boxes and even a few check boxes that, once submitted, will fill out the spreadsheet and hopefully eventually even populate a word document with the relevant information. As of this moment, if certain text boxes, combo boxes and check boxes are left blank and the userform is submitted, a separate message for each one will appear and say something specific like "Please fill the first name field!" and have an "ok" button at the bottom. my current problem is that though it shows the message box when there is an empty cell, if i hit the "ok" button the code will continue on and hide the userform (which i want to happen if all the boxes contain data) instead of allowing me to fill that blank text box and re-submit the userform. I've searched around a lot and tried a few different things but i think my lack of basic programming knowledge is holding me back. If anyone can give me a shove in the right direction it would be appreciated!


    Here is the code I've come up with so far (any pointers on it in general would also be appreciated though i plan to eventually open a thread in the code review section once its working properly)



    Private Sub CommandButton1_Click()

    Dim rw As Integer

    Dim ws As Worksheet

    Set ws = Worksheets("sheet4")

    rw = ws.Cells.Find(what:="*", searchorder:=xlRows, searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1


    If Trim(Me.TextBox2.Value) = "" Then

    MsgBox "Please Fill The First Name Field!"

    End If

    If Trim(Me.TextBox3.Value) = "" Then

    MsgBox "Please Fill The Last Name Field!"

    End If

    If Trim(Me.TextBox5.Value) = "" Then

    MsgBox "Please Fill The Address Field!"

    End If

    If Trim(Me.ComboBox1.Value) = "" Then

    MsgBox "Please Select A Fabric Material Type!"

    End If

    If Trim(Me.ComboBox2.Value) = "" Then

    MsgBox "Please Select A Tax Location!"

    End If

    If Trim(Me.TextBox6.Value) = "" Then

    MsgBox "Please Fill The Track Space Feet Field!"

    End If

    If Trim(Me.TextBox7.Value) = "" Then

    MsgBox "Please Fill The Track Space Inches Field!"

    End If

    If Trim(Me.TextBox8.Value) = "" Then

    MsgBox "Please Fill The Track Length Feet Field!"

    End If

    If Trim(Me.TextBox9.Value) = "" Then

    MsgBox "Please Fill The Track Length Inches Field!"

    End If


    ws.Cells(rw, 1).Value = Me.TextBox1.Value

    Me.TextBox1.SetFocus

    ws.Cells(rw, 2).Value = Me.TextBox10.Value

    Me.TextBox10.SetFocus

    ws.Cells(rw, 3).Value = Me.TextBox2.Value

    Me.TextBox2.SetFocus

    ws.Cells(rw, 4).Value = Me.TextBox3.Value

    Me.TextBox3.SetFocus

    ws.Cells(rw, 5).Value = Me.TextBox5.Value

    Me.TextBox5.SetFocus

    ws.Cells(rw, 6).Value = Me.TextBox4.Value

    Me.TextBox4.SetFocus

    ws.Cells(rw, 7).Value = Me.ComboBox2.Value

    Me.ComboBox2.SetFocus

    ws.Cells(rw, 8).Value = Me.ComboBox1.Value

    Me.ComboBox1.SetFocus

    ws.Cells(rw, 9).Value = Me.CheckBox1.Value

    Me.CheckBox1.SetFocus

    ws.Cells(rw, 10).Value = Me.CheckBox2.Value

    Me.CheckBox2.SetFocus

    ws.Cells(rw, 11).Value = Me.TextBox6.Value

    Me.TextBox6.SetFocus

    ws.Cells(rw, 12).Value = Me.TextBox7.Value

    Me.TextBox7.SetFocus

    ws.Cells(rw, 13).Value = Me.TextBox8.Value

    Me.TextBox8.SetFocus

    ws.Cells(rw, 14).Value = Me.TextBox9.Value

    Me.TextBox9.SetFocus


    UserForm1.Hide


    Dim wrdApp As Word.Application

    Dim wrdDoc As Word.Document

    Dim i As Integer

    Set wrdApp = CreateObject("Word.Application")

    wrdApp.Visible = True

    Set wrdDoc = wrdApp.Documents.Open("\\SERVER\Documents\2020\Excell crap\test.docx")

    End Sub

  • The quick fix is to add

    Code
    1. Exit Sub

    after each Msgbox "Please fill in...field" statement. Not sure why you have the me.TextboxX.SetFocus statements where you do though. They would be better just after the relevant Msgbox statement, so for each text box check:

    Code
    1. If Trim(Me.TextBoxX.Value) = "" Then
    2. MsgBox "Please Fill The ... Field!"
    3.     Me.TextBoxX.SetFocus
    4. Exit Sub
    5. End If


    HTH

  • You can check each TextBox with a Loop.