Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Private Sub cmdClose_Click()
    
    If Me.txtCAPA = vbNullString And Me.cboCustomer = vbNullString And Me.txtAction = vbNullString And Me.cboLocation = vbNullString Then ' from here it would allow the form to close as normal.
    
    Else: MsgBox "are you sure you want to close?  All previously entered data will be lost."' if not empty prompt with message box and allow the user to cancel the close form function and go back to form. 
    
    End If
    
    End Sub
    Thanks for any help here.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,506
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

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

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    ' Close the form WITHOUT saving or copying any of the data to the spreadsheet
    Private Sub cmdClose_Click()
    ' Check to see if fields have data and if so and "close form" is clicked have it ask if you really want to close.
    
    
        If Me.txtCAPA = vbNullString And Me.cboLocation = vbNullString And Me.cboCustomer = vbNullString And Me.txtProblem = vbNullString And Me.txtAction = vbNullString _
            And Me.cboIssuedBy = vbNullString And Me.cboIssuedTo = vbNullString And Me.cboOnBehalfOf = vbNullString And Me.cboIssuedTo2 = vbNullString _
            And Me.txtCostProd = vbNullString And Me.txtCostShip = vbNullString And Me.txtCostConcess = vbNullString And Me.txtCostTravel = vbNullString _
            And Me.txtCostFacility = vbNullString And Me.txtCostOther = vbNullString And Me.txtNotes = vbNullString Then
                Else: 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."
                Exit Sub
        End If
      Unload Me
    
    
    Worksheets("Seatex Incident Log").Activate
    
    
    End Sub

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,506
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

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

    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 by My Aswer Is This; Oct 3rd, 2018 at 10:10 AM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!


  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,506
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

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

    So just tell me.
    What are the name of the two textboxes you do not want checked.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  7. #7
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    txtIncidentID
    DTPicker1

    Thank you... sorry for the confusion.

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,506
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

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

    Quote Originally Posted by kbishop94 View Post
    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  9. #9
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by My Aswer Is This View Post
    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

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,506
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

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

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •