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

kbishop94

Board Regular
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.
 

My Aswer Is This

Well-known Member
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
 

kbishop94

Board Regular
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.

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]
 

My Aswer Is This

Well-known Member
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:

kbishop94

Board Regular
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!

 

kbishop94

Board Regular
txtIncidentID
DTPicker1

Thank you... sorry for the confusion.
 

My Aswer Is This

Well-known Member
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top