Consulting

Results 1 to 8 of 8

Thread: VBA -Identify blank fields for user form before submitting -Force User to add data

  1. #1
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location

    Wink VBA -Identify blank fields for user form before submitting -Force User to add data

    Hello all
    I have created a User form which is working . I need assistance with VBA code that creates an error message if any of the fields are left blank when they active the "Send "or Save"button .

    Ideally for each text box or combo box that is blank .It is my hope that the blank field could be highlighted in a colour and an error message alerting that info is required and cant submit without adding text or selection
    I haven't been able to find this info in the forums , possibly using incorrect terminology.

    I though that I could omit this process but I have users that continually missing steps and getting agro . I think its the User they feel its my form....Anyway I would like to force Users to enter into all fields

    I appreciate that my coding is not elegant but at least it is functional
    Hoping someone can provide guidance and education on this
    Appreciation and thanks in advance

      Private Sub CommandButton3_Click()Unload Me
    End Sub
    
    
    Private Sub CommandButton4_Click()
    Dim fileName As String
    fileName = "Q:SADS_ADH\ADH GPU\Electronic Interp GPU\GPU master interp.xlsm"
    
    
    'Call function to check if the file is open
    If IsFileOpen(fileName) = False Then
    
    
        'Insert actions to be performed on the closed file
                 MsgBox " Masterspreadsheet is closed PLEASE PROCEED."
        
    
    
    Else
    
    
        'The file is open or another error occurred
        MsgBox " Masterspreadsheet is open.PLEASE TRY AGAIN LATER."
    
    
    End If
    
    
    End Sub
    
    
    Private Sub CommandButton6_Click()
    'RESET FORM FOR NEXT REQUEST
    
    
        'TextBox1.Value = ""
        'TextBox2.Value = ""
        
        TextBox3.Text = Format(Now(), "DD-MMM-YY")
        ComboBox1.value = ""
        TextBox6.value = ""
        TextBox7.value = ""
        ComboBox2.value = ""
        TextBox5.value = ""
        TextBox8.value = ""
        TextBox9.value = ""
        TextBox16.value = "60"
        TextBox11.value = ""
        ComboBox4.value = ""
        ComboBox5.value = ""
        TextBox14.value = ""
        
        'TextBox12.Value = ""
        
        TextBox13.value = ""
    End Sub
    
    
    Private Sub TextBox11_Change()
    Me.TextBox11.value = Application.WorksheetFunction.Proper(Me.TextBox11.value)
    End Sub
    
    
    Private Sub TextBox15_Change()
    
    
    End Sub
    
    
    Private Sub TextBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'initiate the pop up calendar with double click in this textbox
        Dim datevalue As Date
    datevalue = CalendarForm.GetDate
    If datevalue = "12:00:00 AM" Then
        'calendar was closed without picking a date
        TextBox3.Text = ""
    Else
        'format the picked date for the textbox
        TextBox3.Text = Format(datevalue, "DD-MMM-YY")
    End If
    End Sub
    
    
    Private Sub TextBox6_Change()
    Me.TextBox6.value = Application.WorksheetFunction.Proper(Me.TextBox6.value)
    End Sub
    
    
    Private Sub TextBox7_Change()
    Me.TextBox7.value = Application.WorksheetFunction.Proper(Me.TextBox7.value)
    End Sub
    
    
    Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Hr = Int(Me.TextBox9 / 100)
    Min = Me.TextBox9 Mod 100
    Sec = 0
    Me.TextBox9 = Format(TimeSerial(Hr, Min, Sec), "h:mm AM/PM")
    Range("A1").value = TimeSerial(Hr, Min, Sec)
    Range("A1").NumberFormat = "h:mm AM/PM"
    End Sub
    
    
    'Enter number for UR , format set as number
    
    
    Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
    KeyAscii = KeyAscii
    Else
    KeyAscii = 0
    MsgBox "Invalid key pressed,enter Number"
    End If
    End Sub
    Private Sub TextBox8_Enter()
    'initiate pop up calendar when entering the text box
        Dim datevalue As Date
    datevalue = CalendarForm.GetDate
    If datevalue = "12:00:00 AM" Then
        'calendar was closed without picking a date
        TextBox8.Text = ""
    Else
        'format the picked date for the textbox
        TextBox8.Text = Format(datevalue, "DD-MMM-YY")
    End If
    End Sub
    
    
    Private Sub UserForm_Initialize()
    
    
        ComboBox1.AddItem "REQUEST"
        ComboBox1.AddItem "CANCEL"
        
        ComboBox2.AddItem "Male"
        ComboBox2.AddItem "Female"
        
        'ComboBox3.AddItem "GPU"
        'ComboBox3.AddItem "SRU"
        'ComboBox3.AddItem "SNU"
        'ComboBox3.AddItem "OMS"
        'ComboBox3.AddItem "ORTHO"
        
        ComboBox4.AddItem "PF"
        ComboBox4.AddItem "RECEP"
       
        ComboBox5.AddItem "Miss"
        ComboBox5.AddItem "Mr"
        ComboBox5.AddItem "Mrs"
        ComboBox5.AddItem "Ms"
        
        ComboBox6.AddItem "Male"
        ComboBox6.AddItem "Female"
        
        ComboBox7.AddItem "A"
        ComboBox7.AddItem "B"
        ComboBox7.AddItem "C"
        ComboBox7.AddItem "D"
        ComboBox7.AddItem "E"
        ComboBox7.AddItem "F"
        ComboBox7.AddItem "G"
        ComboBox7.AddItem "S"
        ComboBox7.AddItem "ORAL DIAG"
        
        Me.TextBox3.Text = Format(Now(), "DD-MMM-YY")
        
    End Sub
    Private Sub CommandButton1_Click()
    Dim irow As Long
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim dte
    Set ws = Worksheets("Interpreter Requests")
    
    
    'find first row in database TO WRITE TO
    irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
            .Range("A" & irow).value = Me.ComboBox1.Text
            
            'to ensure real dates on sheet not text looking like dates
             'dte = Split(Me.TextBox3.Text, "/")
            '.Range("B" & irow).value = DateSerial(dte(2), dte(1), dte(0))
            '.Range("B" & irow).NumberFormat = "DD/MM/YYYY"
            .Range("B" & irow).value = Me.TextBox3.value
            .Range("C" & irow).value = Me.TextBox6.Text
            .Range("D" & irow).value = Me.TextBox7.Text
            .Range("E" & irow).value = Me.ComboBox2.Text
            .Range("F" & irow).value = Me.TextBox5.Text
            'to ensure real dates on sheet not text looking like dates
             'dte = Split(Me.TextBox8.Text, "/")
            '.Range("G" & irow).NumberFormat = "DD/MMM/YYYY"
            .Range("G" & irow).value = Me.TextBox8.value
            .Range("H" & irow).value = Me.TextBox9.Text
            .Range("I" & irow).value = Me.TextBox13.Text
            .Range("J" & irow).value = Me.ComboBox7.Text
            .Range("K" & irow).value = Me.TextBox16.Text
            .Range("L" & irow).value = Me.TextBox11.Text
            .Range("M" & irow).value = Me.TextBox15.Text
            .Range("N" & irow).value = Me.TextBox14.Text
            .Range("O" & irow).value = Me.ComboBox4.Text
            
         
    End With
    
    
    'RESET FORM FOR NEXT REQUEST
    
    
        'TextBox1.Value = ""
        'TextBox2.Value = ""
        
        'TextBox3.Text = Format(Now(), "DD-MMM-YY")
       ' ComboBox1.value = ""
       ' TextBox6.value = ""
       ' TextBox7.value = ""
       ' ComboBox2.value = ""
       ' TextBox5.value = ""
       ' TextBox8.value = ""
        'TextBox9.value = ""
       ' 'TextBox16.value = "60"
       ' TextBox11.value = ""
        'ComboBox4.value = ""
        'ComboBox5.value = ""
       ' TextBox14.value = ""
        
        'TextBox12.Value = ""
        
        'TextBox13.value = ""
        
        
    End Sub
    
    
    Private Sub CommandButton5_Click()
    
    
    Application.ScreenUpdating = False
    
    
    'Change Workbook
    Dim wb As Workbook
    Set wb = Workbooks.Open("Q:SADS_ADH\ADH GPU\Electronic Interp GPU\GPU master interp.xlsm")
    
    
    
    
    Dim emptyRow As Long
    
    
    'Make Daily_Tracking_Dataset active
    'nwb.Sheets("daily_tracking_dataset").Activate
    'nwb.emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    
    
    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(wb.Sheets("GPU master Interp").Range("A:A")) + 1
    
    
    'Transfer Information
    
    
    With wb.Sheets("GPU master Interp")
    
    
    
    
          
    .Cells(emptyRow, 1).value = ComboBox1.value
    .Cells(emptyRow, 2).value = TextBox3.value
    .Cells(emptyRow, 3).value = TextBox6.value
    .Cells(emptyRow, 4).value = TextBox7.value
    '.Cells(emptyRow, 6).NumberFormat = "DD-MMM-YY"
    .Cells(emptyRow, 5).value = ComboBox2.value
    .Cells(emptyRow, 6).value = TextBox5.value
    .Cells(emptyRow, 7).value = TextBox8.value
    .Cells(emptyRow, 8).value = TextBox9.value
    .Cells(emptyRow, 9).value = TextBox13.value
    .Cells(emptyRow, 10).value = ComboBox7.value
    .Cells(emptyRow, 11).value = "60 min"
    '.Cells(emptyRow, 10).value = TextBox1.value
    .Cells(emptyRow, 12).value = TextBox11.value
    .Cells(emptyRow, 13).value = "GPU: Level 11"
    .Cells(emptyRow, 14).value = TextBox14.value
    .Cells(emptyRow, 15).value = ComboBox4.value
    
    
    
    
    '.Cells(emptyRow, 11).value = ComboBox6.value
    '.Cells(emptyRow, 12).value = TextBox5.value
    '.Cells(emptyRow, 13).value = TextBox6.value
    '.Cells(emptyRow, 14).value = TextBox7.value
    '.Cells(emptyRow, 15).value = ComboBox2.value
    '.Cells(emptyRow, 17).value = ComboBox1.value
         
    
    
    
    
    End With
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
    End Sub
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    add this function to your form
    Function CheckFormControls() As Boolean
        Dim oCtl As MSForms.Control
        
        CheckFormControls = True
        With Me
            For Each oCtl In .Controls
                With oCtl
                    If TypeName(oCtl) = "TextBox" Then
                        If .Text = "" Then
                            CheckFormControls = False
                            .BackColor = vbRed
                        Else
                            .BackColor = &H80000005
                        End If
                    ElseIf TypeName(oCtl) = "ComboBox" Then
                        If .ListIndex = -1 Then
                            CheckFormControls = False
                            .BackColor = vbRed
                        Else
                            .BackColor = &H80000005
                        End If
                    End If
                End With
            Next
        End With
    End Function
    Add this code when you need to check for validity at the top of your button code (your SEND and SAVE buttons)
        If Not CheckFormControls Then
            MsgBox "Please correct error"
            Exit Sub
        End If

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    In addition

    1. You might consider making fields like Action, Gender, etc. option buttons instead of dropdowns

    2. If you exit TIME OF APPPT (which is misspelt) with it blank, you get a Type Mismatch error
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    Hey Jwan and Paul
    Looks like that this has done the trick .Whhoooohoooo ! If the Red is not enough to alert them, then I am not sure what will. Hoping all will go well on Monday when I take to work to try and check file transfer to network spreadsheets
    Paul ahh nuts no one has even recognize the spelling error . Thanks for alerting me to the fact ! I hadn't thought of radio buttons . Is there an advantage and how would the code go if they were radio buttons i.e if there were no gender , action selected . Just curious and good for my education.
    Thank you both for your assistance its really appreciated . Now no one can say " I filled the form out fully its your form !'

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by mml View Post
    Hey Jwan and Paul
    Paul ahh nuts no one has even recognize the spelling error . Thanks for alerting me to the fact ! I hadn't thought of radio buttons . Is there an advantage and how would the code go if they were radio buttons i.e if there were no gender , action selected . Just curious and good for my education.
    Dropdowns could work, but the way I like to do it is to use Option Buttons in a Frame if there is a fixed number of choices defined in advance and only one can be selected

    e.g.

    ( ) Male
    ( ) Female

    User selects one, and the other(s) are cleared
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    Hello
    Before anyone says i cross posted let me explain . I was not able to get to this site at all , and posted on another site . I did try to indicate that had another request in but didnt fully explain ( on other site)
    I understand that cross posting causes problems and wasn't my intention and am aware of the rules . I appreciate all assistance however I was not sure how long the restriction to VBA express would last .
    However it did provide me with two excellent solutions.
    i hope for your understanding in this instance

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @JKwan

    Sub M_snb()
       For Each it In Controls
          Select Case TypeName(it)
          Case "TextBox", "ComboBox"
             it.BackColor = vbRed + 250 * (it.Value = "")
          End Select
        Next
    End Sub

  8. #8
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    @snb, thank you for the code. Very nice!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •