none
Message Box displays twice RRS feed

  • Question

  • Hello -

    I am having a problem with the below message box appearing twice? Not sure why this is happening. Anyone have any ideas?

    Private Sub Term_ChkBx_Click()

            Application.ScreenUpdating = False
            
            If Sheets("Employee Change Form").Range("L6").Value = "" Then
                MsgBox "You must enter a First Name before proceeding"
                Sheets("Employee Change Form").Unprotect Password:=""
                    Sheets("Employee Change Form").Range("L6").Select
                        With Selection.Interior 'yellow
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .Color = 13434879
                            .TintAndShade = 0
                            .PatternTintAndShade = 0
                        End With
                Sheets("Employee Change Form").Term_ChkBx.Value = False
                Sheets("Employee Change Form").Protect Password:=""
                    Exit Sub
                    End If

    End Sub

    Monday, December 18, 2017 7:04 PM

Answers

  • Hi,

    I guess "Term_ChkBx" is a CheckBox on the sheet "Employee Change Form".  If this is correct, your issue is caused from changing the value of "Term_ChkBx".
    This code will resolve your issue.
    If Sheets("Employee Change Form").Range("L6").Value = "" Then
        If (Term_ChkBx.Value = True) Then
            MsgBox "You must enter a First Name before proceeding"
        End If
        Sheets("Employee Change Form").Unprotect Password:=""
        ....
    Regards,

    Ashidacchi

    P.S.
    I'm wondering why you use a CheckBox. Why do you use a Button, instead of CheckBox?
    • Edited by Ashidacchi Tuesday, December 19, 2017 12:42 AM
    • Marked as answer by rstreets2 Tuesday, December 19, 2017 1:31 AM
    Tuesday, December 19, 2017 12:40 AM

All replies

  • Hi,

    I guess "Term_ChkBx" is a CheckBox on the sheet "Employee Change Form".  If this is correct, your issue is caused from changing the value of "Term_ChkBx".
    This code will resolve your issue.
    If Sheets("Employee Change Form").Range("L6").Value = "" Then
        If (Term_ChkBx.Value = True) Then
            MsgBox "You must enter a First Name before proceeding"
        End If
        Sheets("Employee Change Form").Unprotect Password:=""
        ....
    Regards,

    Ashidacchi

    P.S.
    I'm wondering why you use a CheckBox. Why do you use a Button, instead of CheckBox?
    • Edited by Ashidacchi Tuesday, December 19, 2017 12:42 AM
    • Marked as answer by rstreets2 Tuesday, December 19, 2017 1:31 AM
    Tuesday, December 19, 2017 12:40 AM
  • Hi Ashidacchi -

    Thank you so much for your assistance. Your suggestion worked like a charm! Thank you, thank you, thank you!

    as for your "ps" question... I am using a checkbox as this is used in conjunction with another checkbox. Users would have to select one of the checkboxes; which also triggers a bunch of other stuff (code) I did not include in my original post. the rest of code works. I just kept getting these message boxes popping up twice. I also use the checkbox as I need to know what the users choice is for some other functions.

    Again thank you for your assistance. Your help if very much appreciated!
    Tuesday, December 19, 2017 1:31 AM
  • Hello rstreets2,

    This event should trigger once you changed value of the check box and in the event you also use the code

     Sheets("Employee Change Form").Term_ChkBx.Value = False

    to change the value.

    So once you try to change the checkbox from false to true. The event triggers and then in the event you changed the value to false again, the event will trigger again. I would suggest you use EnableEvents to prevent the event trigger temporary. Here is the example.

    Private Sub Term_ChkBx_Click()
      Application.ScreenUpdating = False
      Application.EnableEvents = False
            If Sheets("Employee Change Form").Range("L6").Value = "" Then
                MsgBox "You must enter a First Name before proceeding"
                Sheets("Employee Change Form").Unprotect Password:=""
                    Sheets("Employee Change Form").Range("L6").Select
                        With Selection.Interior 'yellow
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .Color = 13434879
                            .TintAndShade = 0
                            .PatternTintAndShade = 0
                        End With
                Sheets("Employee Change Form").Term_ChkBx.Value = False
                Sheets("Employee Change Form").Protect Password:=""
                    Exit Sub
                    End If
                Application.EnableEvents = True
                Application.ScreenUpdating = True
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 19, 2017 1:32 AM
  • Hi Terry and thank you for your reply!

    I actually did try it with the EnableEvents method you suggested. I even tried it in different locations etc., but still came up with the same results; message box displaying twice :(

    Ashidacchi's suggestion, however, did do the trick, even with the EnableEvents not even present.I do thank you for your time and suggestion. You have been a life saver for me Terry! I always appreciate your help and suggestions.

    Thanks again Terry and hope you have a wonderful Holiday Season :)

    Tuesday, December 19, 2017 1:58 AM
  • Hi rsteets2,

    Thank you for marking my post as an answer and explaining about my question.
    I could understand your situation. Thank you too.

    Regards,

    Ashidacchi

    Tuesday, December 19, 2017 1:59 AM