No blanks in User form - Force users to complete - highlight blank fields

Rancidveg · Jul 22, 2020

Hello All
I am starting this thread because I am in some urgent need of assistance and VBA express site seems to be down
I have a user form that I would like any unfilled txt box or combo boxes that are not filled i.e left blank to prompt an error message and highlight the field for the user
The form data is saved to the second tab in the worksheet and also to another separate worksheet . The two command buttons used are "Send "and "save'. If either of these buttons are used I would like if possible the alert msg to pop up .
Attached is a dump of all the fields that need the prompt. Also provided my VBA for the form its not elegant but is working
Hoping that there is a simple way to do this. Thanks in advance

VBA Code:

Private Sub CommandButton3_Click()
Unload Me
End Sub

Private Sub CommandButton4_Click()
Dim fileName As String
fileName = "Q:SADS_ADHx\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_ADHx\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

Yongle · Jul 23, 2020

Would it be helpful to the user to force every value in the userform to be entered before clicking on Send or Save
Whilst a message at the end may still be required, it is fairly simple to catch empty textboxes and comboboxes and send the user back

Simple example
- takes user back to textbox1 if value in TextBox3 is amended when textbox1 contains no value

VBA Code:

Private Sub TextBox3_Change()
    If Len(TextBox1.Text) = 0 Then
        TextBox3 = ""
        TextBox1.SetFocus
    End If
End Sub

Yongle · Jul 23, 2020

Rancidveg said:
I have a user form that I would like any unfilled txt box or combo boxes that are not filled i.e left blank to prompt an error message and highlight the field for the user

You could prevent a command button from being clicked until all required values are entered by using its MouseMove event

Model for you to follow
The code below tests the values in TextBox1, TextBox3, ComboBox1 and will not allow CommandButton5 to be clicked until all 3 contain a value
The user is sent back to each empty object in turn (in sequence listed in the array)

VBA Code:

Private Sub CommandButton5_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    Dim obj As Variant
    For Each obj In Array(TextBox1, TextBox3, ComboBox1)
        If Len(obj.Value) = 0 Then
            MsgBox "Enter a value in active box"
            obj.SetFocus
            Exit Sub
        End If
    Next
End Sub

Rancidveg · Jul 23, 2020

many thanks for the reply
I copied and pasted but throws an error for If Len(obj.value) = 0 Then

VBA Code:

Private Sub CommandButton5_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    Dim obj As Variant
    For Each obj In Array(TextBox1, TextBox3, ComboBox1)
        If Len(obj.value) = 0 Then
            MsgBox "Enter a value in active box"
            obj.SetFocus
            Exit Sub
        End If
    Next
End Sub

Yongle · Jul 23, 2020

It works for me. I am not sure what is different in your userform.
try
If obj.Text = "" Then

If that does not work, what is the error message ?
Would be helpful if you can identify if it's a textbox or a combobox causing the error

Rancidveg · Jul 23, 2020

Can i check that I have pasted the code in the correct place first ?!! Which was just at the top
To trial it I left combo box 1 blank see image and clicked Send

Error is run time 424

VBA Code:

Private Sub CommandButton5_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    Dim obj As Variant
    For Each obj In Array(TextBox1, TextBox3, ComboBox1)
        If Len(obj.value) = 0 Then
            MsgBox "Enter a value in active box"
            obj.SetFocus
            Exit Sub
        End If
    Next
End Sub

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

Yongle · Jul 23, 2020

TextBox1, TextBox3, ComboBox1
Do all 3 objects above exist in your userform?
- must have the same 3 names

Rancidveg · Jul 24, 2020

Looks like rookie mistake added all the info in and it comes with msg box message enter value in active box . tried different fields
Thank you so much this is a great start

Yongle · Jul 24, 2020

Glad to hear that you are making progress

Fluff · Jul 24, 2020

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

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

No blanks in User form - Force users to complete - highlight blank fields

Rancidveg

New Member

Attachments

Excel Facts

Yongle

Well-known Member

Yongle

Well-known Member

Rancidveg

New Member

Yongle

Well-known Member

Rancidveg

New Member

Attachments

Yongle

Well-known Member

Rancidveg

New Member

Yongle

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

No blanks in User form - Force users to complete - highlight blank fields

New Member

Attachments

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Attachments

Well-known Member

New Member

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock