MsgBox if mandatory UserForm fields are not filled in

[NaruZsuzsi]

Hey,

Could you help me please? Although I'm still a beginner but enthusiastic.
I would like to "build" a database. When the excel starts a UserForm pops-up and asking for the information from my group (colleagues). I'd like to force those TextBoxes (and a ComboBox and 2 CheckBoxes and one OptionButton from 2) to be mandatory to fill in. If one or more of those are empty a MsgBox would remind the user. First I planned to list the empty fields but later I simplified my thoughts to a MsgBox with the text stg like this: "You have an empty field left. Please fill all the boxes!". It seemed easier to achieve, but how to do it?
In the first version I found complicated the several possibility. There are 9 TextBoxes, 1 ComboBox, 2 OptionButtons, 2 CheckBoxes. What if 2 or 3 or 4 are empty etc... And wanted to list those to different lines which are empty. Is it possible in an easy way?
Anyhow the second version would be perfect if anyone could help me.




Private Sub EnterDataButton_Click2()

' Dialog's Title
strTitle = "Missing contract information"

If NameTextBox.Value = "" Or PartnerTextBox.Value = "" _
Or EmailTextBox.Value = "" Or ResponsibleTextBox.Value = "" _
Or ContactTextBox.Value = "" Or TypeComboBox.Value = "" _
Or EffectiveTextBox.Value = "" Or ExpiryTextBox.Value = "" _
Or LinkPdfTextBox.Value = "" Then


MsgBox "You must complete all fields, some boxes are still empty! ", vbCritical
Exit Sub


End If




'ContractDataUserForm1.Hide


End Sub



Or have some business with:

'strMsg1 = "You must complete Partner's name"
'strMsg2 = "You must complete Partner's contact person"
'strMsg3 = "You must complete Partner's e-mail address"
'strMsg4 = "You must complete C-level manager"
'strMsg5 = "You must complete contact"
'strMsg6 = "You must select type of contract"
'strMsg7 = "You must complete Effective date"
'strMsg8 = "You must complete Expiry date"
'strMsg9 = "You must complete Link for .pdf"




'MsgBox strMsg1 _
'& vbCrLf & strMsg2 _
'& vbCrLf & strMsg3 _
'& vbCrLf & strMsg4 _
'& vbCrLf & strMsg5 _
'& vbCrLf & strMsg6 _
'& vbCrLf & strMsg7 _
'& vbCrLf & strMsg8 _
'& vbCrLf & strMsg9 _

Please don't laugh on me I read several cases and solutions but none of those related close enough to my "problem".

Thanks in advance if you can help me.

 

[My Aswer Is This]

Try this and see if this will work for you.
Any control which is not selected or filled in.
You will get a Message box and all those controls Back color will be highlighted in red.
If the control is filled in or checked the Back color will be Green.

Listing all the control names in a message Box would be more complicate and I think this approach would be better. I have exclude Command Buttons and Listbox. You should see how I exclude them and add more if needed.
I suggest you put this script in your UserForm and then put this Command in your Command Button which you will click after filling in all your control

Just put "Call Check_Controls" without quotes into you command button. Doing this will launch your script named "Call_Controls

Put this script in your command button or put this script in a Macro in your Userform and use call feature.

Sub Check_Controls()
Dim ans As Long
For Each xcontrol In Me.Controls
        If TypeName(xcontrol) <> "CommandButton" And TypeName(xcontrol) <> "ListBox" Then
            If xcontrol.Value = vbNullString Or xcontrol.Value = False Then
                xcontrol.BackColor = vbRed
                ans = ans + 1
                Else
                 xcontrol.BackColor = vbGreen
               End If
            End If
    
Next xcontrol
If ans > 0 Then MsgBox "Please enter data in the required fields.  Controls in Red need values", vbInformation, "Enter data"
End Sub

 

[NaruZsuzsi]

Hey,

I get what you mean but unfortunately it does not work (properly). I tried to modify it to fit to my system but the result is no success yet. I guess, I strongly suspect it is user error But at least I can learn from this too

It looks like this now:
[FONT=&quot]
[/FONT][FONT=&quot]
[/FONT]
[FONT=&quot][/FONT][FONT=&quot]Private Sub EnterDataButton_Click()[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Dim emptyRow As Long[/FONT]
[FONT=&quot]Dim MyDate As Date[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]MyDate = Now()[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]'Make Sheet "2017" active[/FONT]
[FONT=&quot] Sheets("2017").Activate[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'Enable data insertion when workbook is protected and shows "edit workbook"[/FONT]
[FONT=&quot]If Application.ProtectedViewWindows.Count > 0 Then[/FONT]
[FONT=&quot] Application.ActiveProtectedViewWindow.Edit[/FONT]
[FONT=&quot]End If[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'Determine emptyRow[/FONT]
[FONT=&quot] 'emptyRow = ActiveSheet.UsedRange.Rows.Count + 1[/FONT]
[FONT=&quot] emptyRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row + 1[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'Transfer information[/FONT]
[FONT=&quot] Cells(emptyRow, 2).Value = TypeComboBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 3).Value = NameTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 4).Value = PartnerTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 5).Value = EmailTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 6).Value = PhoneTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 7).Value = ResponsibleTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 8).Value = ContactTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 9).Value = EffectiveTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 10).Value = ExpiryTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 12).Value = LinkPdfTextBox.Value[/FONT]
[FONT=&quot] Cells(emptyRow, 15).Value = CommentsTextBox.Value[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] If FullySignedCheckBox.Value = True Then[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Cells(emptyRow, 13).Value = "Yes"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Cells(emptyRow, 13).Value = "No"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] If HardCopyCheckBox.Value = True Then[/FONT]
[FONT=&quot] Cells(emptyRow, 14).Value = "Yes"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Cells(emptyRow, 14).Value = "No"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] If InkOptionButton.Value = True Then[/FONT]
[FONT=&quot] Cells(emptyRow, 11).Value = "Ink"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] If DocuSignOptionButton.Value = True Then[/FONT]
[FONT=&quot] Cells(emptyRow, 11).Value = "DocuSign"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Call Check_Controls 'This calls in the check of empty controls (controls= textbox, combobox etc)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Unload Me [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]End Sub[/FONT]
[FONT=&quot]
________________________________________________________


[/FONT]
[FONT=&quot]Sub Check_Controls()[/FONT]
[FONT=&quot]Dim ans As Long[/FONT]
[FONT=&quot]Dim xControl As Control

[/FONT]
[FONT=&quot]For Each xcontrol In Me.Controls[/FONT]
[FONT=&quot] If TypeName(xcontrol) <> "EnterDataButton" And TypeName(xcontrol) <> "ListBox" Then[/FONT]
[FONT=&quot] If xcontrol.Value = vbNullString Or xcontrol.Value = False Then[/FONT]
[FONT=&quot] xcontrol.BackColor = vbRed[/FONT]
[FONT=&quot] ans = ans + 1[/FONT]
[FONT=&quot]
Else
xcontrol.BackColor = vbGreen
[/FONT][FONT=&quot] End If[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Next xcontrol[/FONT]
[FONT=&quot]If ans > 0 Then MsgBox "Please enter data in the required fields. Controls in Red need values", vbInformation, "Enter data"[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]End Sub[/FONT]


It marks the red-coloured-line, with Run-time Error '438' (object doesn't support this property or method)


Thanks for your time and help!

 

[My Aswer Is This]

Well when I tested the script it all worked for me.

What I would do is test the script by just entering some data into the controls and see if it works.

Do not put any code in the buttons except for what I gave you then see if it does what you want when data is entered into the controls.


Show me the code you put into the command button it should only be what I gave you for now.

What version of Excel are you using and are you using a Apple computer.

 

[NaruZsuzsi]

Hi,

I use Excel 2016 on a DELL notebook w/ win 10.

Yes, I've tried to type data to some boxes and I've already tried it simply to run the code (with F5) you have provided me. Not only the way pushing the button it is connected to. But it always got stuck at that line I coloured in red. This is why I tried some modifications, tried to personalize your code to my situation.

I'm not convinced if it wasn't my fault, of course. Is it possible you wrote something general which I should substitute to (any) specific information of mine?

What do you think?

Thanks!

Zsuzsi

 

[My Aswer Is This]

The reason your having problems is because you modified my code:
you changed it.

Unless you really know what your doing it is best to not modify my code and then say my code does not work.

The script just looks for certain type controls. the script does not care what the control is named. Controls are things like listbox textbox option box.

It looks to see if any of those controls are empty. If does not need to check and see if Command Buttons are empty.

So what was wrong with my code and why did you think you needed to modify it.

 

[dmt32]

Hi,
You should be able to do what you want.

Not fully tested but based on codes you have published, give following a try & see if goes in right direction for you.

Place following either in a STANDARD module or your Userform’s code page:

 Function IsComplete(ByVal Form As Object, ByRef ControlValues As Variant) As Boolean    
     Dim ctrl() As String, msg As String
    Dim i As Integer
    
    For i = 1 To UBound(ControlValues)
        ctrl = Split(ControlValues(i), ",")
        With Form.Controls(ctrl(0))
        If ctrl(1) <> "Not Required" Then If Not Len(.Text) > 0 Then msg = msg & ctrl(1) & Chr(10)
        Select Case .Name
        Case "FullySignedCheckBox", "HardCopyCheckBox"
        ControlValues(i) = IIf(.Value, "Yes", "No")
        Case "InkOptionButton"
        ControlValues(i) = IIf(.Value, "Ink", "DocuSign")
        Case Else
        If IsDate(.Text) Then
        ControlValues(i) = DateValue(.Value)
        Else
        ControlValues(i) = .Value
        End If
        End Select
        End With
    Next i
    If Len(msg) > 0 Then
        MsgBox "The Following Fields Must Be Completed:" & Chr(10) & Chr(10) & msg, 48, "Required Entry"
    Else
        IsComplete = True
    End If
End Function

Place following in your userform’s CODE PAGE

Option Base 1
Private Sub EnterDataButton_Click()
    Dim DataEntry As Variant
    Dim ws2017 As Worksheet
    Dim NextRow As Long
    
    
    Set ws2017 = ThisWorkbook.Worksheets("2017")
    
    DataEntry = Array("TypeComboBox,Type of Contract", "NameTextBox,Partner's name", "PartnerTextBox,Partner's Contact Person", _
                "EmailTextBox,Partner's e-mail Address", "PhoneTextBox,Not Required", "ResponsibleTextBox,C-level Manager", _
                "ContactTextBox,Contact", "EffectiveTextBox,Effective Date", "ExpiryTextBox,Expiry Date", _
                "InkOptionButton,Not Required", "LinkPdfTextBox,Link for .pdf", "FullySignedCheckBox,Not Required", _
                "HardCopyCheckBox,Not Required", "CommentsTextBox,Not Required")
                
    If Not IsComplete(Me, DataEntry) Then Exit Sub
    
    With ws2017
        NextRow = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
        .Cells(NextRow, 2).Resize(, 14).Value = DataEntry
    End With
    
    MsgBox "Data Entry Complete.", 48, "Entry Complete"
    
    Unload Me
End Sub

Note Option Base 1 statement which MUST be placed at the VERY TOP of your forms code page OUTSIDE any procedure.

Hope Helpful

Dave

 

[NaruZsuzsi]

Hiya,

As I previousely wrote: "... But it always got stuck at that line I coloured in red. This is why I tried some modifications, tried to personalize your code to my situation. ..."

So I'm sorry about the modifications but your basic version didn't want to work FOR ME. This DOES NOT mean your code wasn't good. It seems good, anyhow it does not work in my table I am sad about it. By now my question is solved, nevertheless later I will figure out what have been the problem here...

Thank you very much for your help! You don't have to take my head off...

Have a nice time!

Zsuzsi

 

[NaruZsuzsi]

Hi Dave!

Thank you very very much, for your solution. This works for me and I like it like hell!

Have a nice day!

Zsuzsi

 

[dmt32]

Hi Dave!

Thank you very very much, for your solution. This works for me and I like it like hell!

Have a nice day!

Zsuzsi

Hi,
Glad solution worked ok for you.

After I posted I noticed that section in Function to test for date values was not written too well as it would allow invalid dates to be entered in your date textboxes

Try this update to the function

 Function IsComplete(ByVal Form As Object, ByRef ControlValues As Variant) As Boolean    
    Dim ctrl() As String, msg As String
    Dim i As Integer
    
    For i = 1 To UBound(ControlValues)
        ctrl = Split(ControlValues(i), ",")
        With Form.Controls(ctrl(0))
        
        If ctrl(1) <> "Not Required" Then If Not Len(.Text) > 0 Then msg = msg & ctrl(1) & Chr(10)
        
        Select Case .Name
        Case "FullySignedCheckBox", "HardCopyCheckBox"
            ControlValues(i) = IIf(.Value, "Yes", "No")
        Case "InkOptionButton"
            ControlValues(i) = IIf(.Value, "Ink", "DocuSign")
        Case "EffectiveTextBox", "ExpiryTextBox"
        If IsDate(.Text) Then
            ControlValues(i) = DateValue(.Value)
        Else
           If Len(.Text) > 0 Then msg = msg & ctrl(1) & " (Invalid Date Entry)" & Chr(10)
        End If
        Case Else
            ControlValues(i) = .Value
        End Select
        End With
    Next i
    
    If Len(msg) > 0 Then
        MsgBox "The Following Fields Must Be Completed:" & Chr(10) & Chr(10) & msg, 48, "Required Entry"
    Else
        IsComplete = True
    End If
End Function

If you enter an invalid date in your date textboxes, it should be reported in the msgbox informing you “(Invalid Date Entry)”

Many thanks for feedback, it is very much appreciated.

Dave