VBA for Message Box Upon Active Cell Value

[nc_waggoner]

I have a file with drop downs. The goal is to remind users to enter a response in column K (root cause) if the option "Employee Error" is chosen from the drop down in column H (Cause). How do I make a message box appear saying "Please enter a root cause" once a user chooses "Employee Error" in the drop down? I have tried:

Sub RootCause()

IF ActiveCell.Value = "Employee Error" Then
MsgBox ("Please enter a root cause")

End If
End Sub

However, the message box will not pop up. Thanks for the help!

 

[JoeMo]

You need a worksheet change event macro - not a standard macro if you want an automatic pop up.

Put the code below in the worksheet that has your drop downs following the steps below:
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If LCase(Target.Value) = "employee error" Then
        If Not IsEmpty(Target) And IsEmpty(Target.Offset(0, 3)) Then
            MsgBox "Please enter a root cause in cell: " & Target.Offset(0, 3).Address(0, 0)
            Target.Offset(0, 3).Select
        End If
    End If
End If
End Sub

 

[nc_waggoner]

Joe, that did not seem to work. I followed the steps and the code looks fine. Saved it as XLSM, macros are enabled. But when I chose that option from the drop down in Column H, nothing happened.

 

[JoeMo]

Joe, that did not seem to work. I followed the steps and the code looks fine. Saved it as XLSM, macros are enabled. But when I chose that option from the drop down in Column H, nothing happened.

Did you choose the specific option "Employee Error" per your OP? If yes, you may have inadvertently disabled events. To fix that open your workbook to the sheet with the code. Then press alt + F11 keys to open the VBE window. Press ctrl + g keys to open the Immediate Window, then in the Immediate Window type (w/o the quote marks) "Application.EnableEvents = True" and press enter.

Now change a cell in col H that has an empty cell in col K - use the drop down in col H and select "Employee Error" and you should see the pop up. If not you have most likely not installed the code correctly or your trust center settings disable all macros w/o warning.