Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VBA for Message Box Upon Active Cell Value

  1. #1
    New Member
    Join Date
    Sep 2016
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA for Message Box Upon Active Cell Value

    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!

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for Message Box Upon Active Cell Value

    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.
    Code:
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Sep 2016
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for Message Box Upon Active Cell Value

    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.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for Message Box Upon Active Cell Value

    Quote Originally Posted by nc_waggoner View Post
    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com