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


My bad. I appoligize. Regardless, your code is working fine. I just changed this part from:

Code:
If x > 0 Then

to this:

Code:
If x > [B]1[/B] Then

And that seems to cancel out the 2 controls that autopopulate when the form is opened and doesnt trigger the yes/no prompt in your code when the close button is clicked. Perfect! THanks again, My Aswer Is This
icon14.png
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Well if that works for you great.
I think you will find if one of the other Textboxs have a value in them other then the one you wanted excluded you will not get a warning message.

So for example if the textbox you wanted excluded is empty but TextBox4 has a value in it the script will not give you a warning.
If that proves true you will have to get back with me.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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