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
299
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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

Active Member
Joined
Dec 5, 2016
Messages
299
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
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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

Active Member
Joined
Dec 5, 2016
Messages
299
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
So just tell me.
What are the name of the two textboxes you do not want checked.
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
299
txtIncidentID
DTPicker1

Thank you... sorry for the confusion.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,620
Messages
5,487,915
Members
407,614
Latest member
fuslela

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top