Struggling with Application.EnableEvents

JakeB8

New Member
Joined
Feb 7, 2019
Messages
10
First time poster, and let me apologize right off the bat if I'm asking a question that has already been answered. I've tried searching for an answer but haven't found what I need yet.

I'm also a first-timer to VBA as I'm finishing up my first project. I don't really know coding that well but have been able to pick up everything I've needed by searching around the web. But the very last piece of the puzzle is stumping me.

I've created a form that is populated by the user filling out a series of UserForms which then insert the answers into the form when a "Next" button is clicked, and depending on certain answers changes the way the form looks. It all works pretty well, so no problem there. I also didn't want the user modifying their answers outside of an Edit button that I placed in the form, so I protected the sheet and added additional code that allows the UserForms to still insert the answers. Again, no problems there. Rather than the standard Microsoft notice about the sheet being protected I wanted the user to receive a custom message when they clicked in the protected sheet, so I added this code to the tab where the form exists:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Locked = True Then
    MsgBox "yada, yada, yada"
End If
Application.EnableEvents = True
End Sub
It does what I want it to do but now the custom message shows up whenever the UserForms try to insert the answers. I don't want that to happen so I added Application.EnableEvents = False at the beginning of the "Next" button code on the first UserForm and then returned the value to True at the end.
Code:
Private Sub NextButton1_Click()
Application.EnableEvents = False
'lots of working code here
Application.EnableEvents = True
End Sub
It works, but doesn't seem to return the value back to True. Once I click the "Next" button the custom message doesn't appear when I click the protected sheet. Shouldn't returning the EnableEvents value back to True allow the custom message to show up again?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,140
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
What you have shown is correct, I suspect that the Next button code is not running to completion, possibly due to an error of some description.
Either step through the code & see what happens, or post it here.
 

JakeB8

New Member
Joined
Feb 7, 2019
Messages
10
Thanks, Fluff.

Below is the code for the Next button. Most of it actually refers back to the UserForm to see if any of the data inputs have been left blank. I've tried moving the position of the Application.EnableEvents lines to just before and after the portion of the code that writes the data to the form but that caused a different issue.

Code:
Private Sub NextButton1_Click() 'Clicking "Next" performs the functions listed below.
Application.EnableEvents = False
Sheets("Form").Range("C3").Select 'Sets cell C3 as "home."
If (Me.txtName.Value = "" Or Me.txtDate.Value = "" Or Me.cboUserLoc.Value = "" Or Me.txtCustomer.Value = "" Or Me.txtMake.Value = "" Or Me.txtModel.Value = "" Or Me.txtProgCode.Value = "" Or Me.txtComps.Value = "" Or Me.cboPMP.Value = "" Or Me.cboRequest.Value = "") Then
    'If the user does not add required information to the form an error message will appear and the field(s) will be highlighted pink.
    MsgBox "Please add the missing information to the form.", vbOKOnly, "Error"
        Me.Hide
            If Me.txtName.Value = "" Then
                Me.txtName.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtName.Value <> "" Then
                Me.txtName.BackColor = RGB(255, 255, 255)
            End If
            If Me.txtDate.Value = "" Then
                Me.txtDate.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtDate.Value <> "" Then
                Me.txtDate.BackColor = RGB(255, 255, 255)
            End If
            If Me.cboUserLoc.Value = "" Then
                Me.cboUserLoc.BackColor = RGB(255, 225, 225)
                ElseIf Me.cboUserLoc.Value <> "" Then
                Me.cboUserLoc.BackColor = RGB(255, 255, 255)
            End If
            If Me.txtCustomer.Value = "" Then
                Me.txtCustomer.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtCustomer.Value <> "" Then
                Me.txtCustomer.BackColor = RGB(255, 255, 255)
            End If
            If Me.txtMake.Value = "" Then
                Me.txtMake.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtMake.Value <> "" Then
                Me.txtMake.BackColor = RGB(255, 255, 255)
            End If
            If Me.txtModel.Value = "" Then
                Me.txtModel.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtModel.Value <> "" Then
                Me.txtModel.BackColor = RGB(255, 255, 255)
            End If
            If Me.txtProgCode.Value = "" Then
                Me.txtProgCode.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtProgCode.Value <> "" Then
                Me.txtProgCode.BackColor = RGB(255, 255, 255)
            End If
            If Me.txtComps.Value = "" Then
                Me.txtComps.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtComps.Value <> "" Then
                Me.txtComps.BackColor = RGB(255, 255, 255)
            End If
            If Me.cboPMP.Value = "" Then
                Me.cboPMP.BackColor = RGB(255, 225, 225)
                ElseIf Me.cboPMP.Value <> "" Then
                Me.cboPMP.BackColor = RGB(255, 255, 255)
            End If
            If Me.cboRequest.Value = "" Then
                Me.cboRequest.BackColor = RGB(255, 225, 225)
                ElseIf Me.cboRequest.Value <> "" Then
                Me.cboRequest.BackColor = RGB(255, 255, 255)
            End If
        ChgMgmtRqstForm1.Show
End If
If (Me.txtName.Value <> "" And Me.txtDate.Value <> "" And Me.cboUserLoc.Value <> "" And Me.txtCustomer.Value <> "" And Me.txtMake.Value <> "" And Me.txtModel.Value <> "" And Me.txtProgCode.Value <> "" And Me.txtComps.Value <> "" And Me.cboPMP.Value <> "" And Me.cboRequest.Value <> "") Then
    'Once the necessary information is added and the "Next" button is clicked the field(s) will return to white.
    Me.txtName.BackColor = RGB(255, 255, 255)
    Me.txtDate.BackColor = RGB(255, 255, 255)
    Me.cboUserLoc.BackColor = RGB(255, 255, 255)
    Me.txtCustomer.BackColor = RGB(255, 255, 255)
    Me.txtMake.BackColor = RGB(255, 255, 255)
    Me.txtModel.BackColor = RGB(255, 255, 255)
    Me.txtProgCode.BackColor = RGB(255, 255, 255)
    Me.txtComps.BackColor = RGB(255, 255, 255)
    Me.cboPMP.BackColor = RGB(255, 255, 255)
    Me.cboRequest.BackColor = RGB(255, 255, 255)
End If
ActiveCell.Offset(0, 0).Value = Me.txtName.Value 'Adds the user's name to cell C3.
ActiveCell.Offset(1, 0).Value = Me.txtDate.Value 'Adds the date to cell C4.
ActiveCell.Offset(2, 0).Value = Me.cboUserLoc.Value 'Adds the user's location to cell C5.
ActiveCell.Offset(3, 0).Value = Me.txtCustomer.Value 'Adds the customer name to cell C6.
ActiveCell.Offset(4, 0).Value = Me.txtMY.Value & " " & Me.txtMake.Value & " " & Me.txtModel.Value & " " & "(" & Me.txtProgCode.Value & ")"
    'Adds the vehicle information to cell C7 in "MY Make Model (Program Code)" format.
ActiveCell.Offset(5, 0).Value = Me.txtComps.Value 'Adds the affected components to cell C8.
ActiveCell.Offset(6, 1).Value = Me.cboPMP.Value 'Adds the most recent PMP Gate to cell D9.
ActiveCell.Offset(7, 1).Value = Me.cboRequest.Value 'Adds the source of the change request to cell D10.
Me.Hide 'Closes the "Request Information" data form.
If Me.cboRequest.Value = Sheets("Data").Range("C1").Value Then 'If the source of the change request is the customer...
    ActiveCell.Offset(8, -2).Value = "What is the customer's Change Number?" 'Adds text to cell A11.
    ActiveCell.Offset(9, -2).Value = "What is the customer's requested implementation date?" 'Adds text to cell A12.
    ChgMgmtRqstForm2.Show 'Loads the first option of the second data form, "Request Info - Customer directed."
    ElseIf Me.cboRequest.Value = Sheets("Data").Range("C2").Value Then 'If the source of the change request is a supplier...
    Sheets("Form").Range("A11:D11").Interior.Color = RGB(192, 192, 192) 'Turns cells A11 through D11 gray.
    ActiveCell.Offset(9, -2).Value = "What date will supplier provide new/modified component(s)?" 'Adds text to cell A12.
    ChgMgmtRqstForm3.Show 'Loads the second option of the second data form, "Request Info - Supplier directed."
    ElseIf Me.cboRequest.Value = Sheets("Data").Range("C3").Value Then 'If the source of the change request is internal from HFI...
    Sheets("Form").Range("A11:D11").Interior.Color = RGB(192, 192, 192) 'Turns cells A11 through D11 gray.
    ActiveCell.Offset(9, -2).Value = "What is the requested implementation date?" 'Adds text to cell A12.
    ChgMgmtRqstForm4.Show 'Loads the third option of the second data form, "Request Info - HFI directed."
End If
Application.EnableEvents = True
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,140
Office Version
365
Platform
Windows
There's nothing obvious, that I can see that would prevent EnableEvents from being re-enabled.
You will need to step through the code using F8 & see if the code is quitting at any point.
 

JakeB8

New Member
Joined
Feb 7, 2019
Messages
10
That's a pretty sweet tool to use right there! Stepping through the code showed me that the problem wasn't when the UserForm was writing the data to the form, it was in selecting C3 as the home cell. I repositioned the Application.EnableEvents codes around that specific line and got the result I was looking for. I think to simplify things I'll remove that home cell and have the UserForm write specifically to a cell location instead of a distance from the home cell. I don't hink I'll even need the Application.EnableEvents codes in that case.

Thanks for your help, Fluff! Issue solved!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,140
Office Version
365
Platform
Windows
Glad you found the problem & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,452
Messages
5,414,603
Members
403,536
Latest member
JEduardo

This Week's Hot Topics

Top