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

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
455
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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. :()

Code:
[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.
icon14.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

Code:
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
 
Upvote 0
Thank you for the reply
icon14.png
... 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):



1z50swh.jpg


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.

Code:
[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]
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!

1zyhidt.jpg
 
Upvote 0
txtIncidentID
DTPicker1

Thank you... sorry for the confusion.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,741
Members
448,295
Latest member
Uzair Tahir Khan

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