VBA for checking to see if fields in a form are not empty when 'close form' button is executed

[kbishop94]

my form has 2 command buttons at the bottom. One is named cmdAdd for adding the data to the worksheet, and the other is named cmdClose for unloading it and closing out the form.

I want to introduce a "are you sure you want close" msgbox after the cmdClose is clicked and if any of the fields on the form are NOT empty. (so in other words, a type safety net for the user when/if they accidentally hit the 'close form' button instead of the 'add data to log' button after data has been entered on the form.)
If all the fields are indeed null/empty, then the msgbox will not appear and the form will close as intended.
However, if anything has been entered into any of the fields, then the msgbox prompt pops-up allowing the user to choose to either continue and close the form, or cancel and then hit the correct button (add data to log button.)

I was trying to get it to work using this technique, but I dont think its going to work this way... (I need some help. )

[COLOR=#000080]Private Sub[/COLOR] cmdClose_Click()

[COLOR=#000080]If[/COLOR] Me.txtCAPA = vbNullString [COLOR=#000080]And[/COLOR] Me.cboCustomer = vbNullString [COLOR=#000080]And[/COLOR] Me.txtAction = vbNullString [COLOR=#000080]And[/COLOR] Me.cboLocation = vbNullString [COLOR=#000080]Then[/COLOR] [COLOR=#008000]' from here it would allow the form to close as normal.[/COLOR]

[COLOR=#000080]Else:[/COLOR] MsgBox "are you sure you want to close?  All previously entered data will be lost."[COLOR=#008000]' if not empty prompt with message box and allow the user to cancel the close form function and go back to form.[/COLOR] 

[COLOR=#000080]End If[/COLOR][COLOR=#0000cd]

[/COLOR][COLOR=#000080]End Sub[/COLOR]

Thanks for any help here.

 

[My Aswer Is This]

Assuming all the controls you want to check are Textboxes.
If not tell me what type controls do we need to check.
Controls are like:
Textbox's
Comboboxes
Check Boxes
Listboxes

Private Sub CommandButton1_Click()
'Modified  10/3/2018  1:49:35 AM  EDT
Dim ctrl As MSForms.Control
 Dim x As Long
 x = 0
For Each ctrl In Me.Controls
    Select Case True
                Case TypeOf ctrl Is MSForms.TextBox
            If ctrl.Value <> "" Then x = x + 1
    End Select
Next ctrl
If x > 0 Then
ans = MsgBox("Are you sure you want to close?  All previously entered data will be lost.", vbYesNo)
If ans = vbYes Then: Unload Me
Else
Unload Me
End If
End Sub

 

[kbishop94]

Thank you for the reply

... I actually have all 3 (text, combo & check) in the form... but I'm not too concerned with the checkboxes (meaning they can be checked or not checked and the form can still close normally... just not a big deal with those and I'm fine with one/all of them being checked or not checked)

Here is a screenshot of the form AFTER the close button is clicked and some text has been entered into the "suggested action" field (near the middle of the form):

So I did get my code to partially work... although not to the extent I would really like. The code below does prompt the user if any (just 1) or all controls have had some data entered (such as a combo box selection or something type in a text box) But, it requires the user to close out the message box then use the red X to close out the form. If everything is blank, the message box doesnt appear and the form closes normally. It would be ideal if the user could hit 'ok' and the form would close, or hit 'no' or 'cancel' and the form would stay open for further changes to be made.

[COLOR=#006400]' Close the form WITHOUT saving or copying any of the data to the spreadsheet[/COLOR]
[COLOR=#000080]Private Sub[/COLOR] cmdClose_Click()
[COLOR=#006400]' Check to see if fields have data and if so and "close form" is clicked have it ask if you really want to close.[/COLOR]


    [COLOR=#000080]If [/COLOR]Me.txtCAPA = vbNullString [COLOR=#000080]And[/COLOR] Me.cboLocation = vbNullString [COLOR=#000080]And [/COLOR]Me.cboCustomer = vbNullString [COLOR=#000080]And[/COLOR] Me.txtProblem = vbNullString And Me.txtAction = vbNullString _
        [COLOR=#000080]And[/COLOR] Me.cboIssuedBy = vbNullString [COLOR=#000080]And [/COLOR]Me.cboIssuedTo = vbNullString [COLOR=#000080]And [/COLOR]Me.cboOnBehalfOf = vbNullString [COLOR=#000080]And[/COLOR] Me.cboIssuedTo2 = vbNullString _
        [COLOR=#000080]And[/COLOR] Me.txtCostProd = vbNullString [COLOR=#000080]And[/COLOR] Me.txtCostShip = vbNullString [COLOR=#000080]And [/COLOR]Me.txtCostConcess = vbNullString [COLOR=#000080]And[/COLOR] Me.txtCostTravel = vbNullString _
        [COLOR=#000080]And [/COLOR]Me.txtCostFacility = vbNullString [COLOR=#000080]And [/COLOR]Me.txtCostOther = vbNullString [COLOR=#000080]And[/COLOR] Me.txtNotes = vbNullString [COLOR=#000080]Then[/COLOR]
            [COLOR=#000080]Else:[/COLOR] MsgBox "Close the form without saving?" & vbCrLf & "All previously entered data will be lost." & _
            vbCrLf & "Hit 'ok' to close this Message Box & click on the Red 'X' in the top right corner."
[COLOR=#000080]            Exit Sub[/COLOR]
[COLOR=#000080]    End If[/COLOR]
  Unload Me


Worksheets("Seatex Incident Log").Activate


[COLOR=#000080]End Sub[/COLOR]

 

[My Aswer Is This]

So tell me why my script did not do what you wanted.
It checks all the textboxes.
What other controls do you want cleared?


You never said from what I can see if you used my script.

 

[kbishop94]

Your code works perfect! ... except... I do have 2 controls on the form that populate automatically when the form is open. DTPicker1 populates with the current date, and a field named txtIncidentID (the one shaded yellow in the screen shot) is populated by looking at the last ID number for the last entry on the spreadsheet and adds 1 to it (and formatted as shown; the "18-" appears in front of the automatically generated & calculated number of '419') So what needs to be tweeked on your supplied code is to exclude those 2 controls when it looks at all the controls to see if they have anything entered. Thanks!

 

[My Aswer Is This]

So just tell me.
What are the name of the two textboxes you do not want checked.

 

[kbishop94]

txtIncidentID
DTPicker1

Thank you... sorry for the confusion.

 

[My Aswer Is This]

txtIncidentID
DTPicker1

Thank you... sorry for the confusion.

Are these both Textboxes ??

DTPicker1

Is this a Textbox or a DatePicker control

DTPicker sounds like a Date Picker control not a Textbox

 

[kbishop94]

Are these both Textboxes ??

DTPicker1

Is this a Textbox or a DatePicker control

DTPicker sounds like a Date Picker control not a Textbox

Yes, DTPicker1 is a date picker.
txtIncidentID is a text box

 

[My Aswer Is This]

My script only included Textboxes.

I thought you said my script worked perfectly but you wanted two textboxes exclude.
I asked what were the names of the Textboxes you wanted excluded and you said:

txtIncidentID
DTPicker1

Well
DTPicker1​

Is not a Textbox.