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

R

Rancidveg

New Member
Joined
Apr 14, 2015
Messages
10
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
 

Attachments

  • 2020-07-22_18-44-44_required fields.jpg
    2020-07-22_18-44-44_required fields.jpg
    109.9 KB · Views: 29
Sort by date Sort by votes
you will see in the first line that I stated that "" I am starting this thread because I am in some urgent need of assistance and VBA express site seems to be down "" This was to indicate that I had posted on VBA express but the site was down . Should have added more info, I can appreciate that this causes issues and was not my intention to "double up " as i had no idea how long the issues with VBA express would last .
having said that it dis provide two excellent solutions
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Put this into practice today at work.. It worked fabulously. Thanks to all who had input to this solution . Really great guys thank you :love:
 
Upvote 0
remember to post your solution on the other forum ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
372
Rhodie72
Rhodie72
M
  • Question Question
Multiple users using same user form having errors and slow at times
Replies
2
Views
542
Joe4
Joe4
K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
377
Kscoof
K
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
351
Fuji
F
P
  • Solved
Code doesn't go on a new line (emptyRow)
Replies
8
Views
404
Fluff
Fluff

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top