none
Message Boxes RRS feed

  • Question

  • Hello All,

    I have a workbook that needs to show message boxes when incorrect inputs have been entered.  I am using data validation at this time but the message boxes/error messages need to be more dynamic than what data validation appears to allow.  So I am looking for VBA code that will let me define all the possible errors and populate a message box with the solution to each error whenever one is entered into the workbook.

    Thank you all very much!

    -Estebanes

    Sunday, September 21, 2014 7:32 PM

Answers

  • Hi,

    According to your description, you want a VBA macro to pop up message boxes when input "possible error" into a cell. 

    In Excel, Worksheet.Change event occurs when cells on the worksheet are changed by the user or by an external link. We can use it to capture the changing and check the value after inputting.

    Since you said you want to define all the possible errors, I think you have to list all the custom "possible errors" before developing such a macro. There is no existing method in Excel Object Model which can help to pick out all input errors. We can only use "If-Else-End If" statement to list all of them.

    Here is a sample which is apply for Range("A1:A10") to make sure user to input value as "x/ot". Intersect method can help us to specify cells which we want to monitor.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        
        If Target.Value <> "x/ot" Then
            MsgBox "please input a correct value such as ""x/ot""!"
        End If
        
        Application.EnableEvents = True
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 23, 2014 5:40 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.
    Monday, September 22, 2014 6:15 AM
  • Hi

    Could you show us a sample of what you want. The answer you're looking for and the different messages if the answer is wrong.

    How many cells with those messages?

    Maybe post a link to a sample file.


    Cimjet

    Monday, September 22, 2014 1:43 PM
  • Hello,

    This application is for a work schedule.  When a manager enters an incorrect input into a cell, for example "ot/x" versus "x/ot", a message box needs to pop up and let the manager know she needs to change "ot/x" to "x/ot".  I am using Excel 2013.  Does this help? Again, many thanks!

    -Estebanes

    Monday, September 22, 2014 6:14 PM
  • Hi,

    According to your description, you want a VBA macro to pop up message boxes when input "possible error" into a cell. 

    In Excel, Worksheet.Change event occurs when cells on the worksheet are changed by the user or by an external link. We can use it to capture the changing and check the value after inputting.

    Since you said you want to define all the possible errors, I think you have to list all the custom "possible errors" before developing such a macro. There is no existing method in Excel Object Model which can help to pick out all input errors. We can only use "If-Else-End If" statement to list all of them.

    Here is a sample which is apply for Range("A1:A10") to make sure user to input value as "x/ot". Intersect method can help us to specify cells which we want to monitor.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        
        If Target.Value <> "x/ot" Then
            MsgBox "please input a correct value such as ""x/ot""!"
        End If
        
        Application.EnableEvents = True
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 23, 2014 5:40 AM
    Moderator
  • I think this is going to work.  I made a slight change to the code (see below).  I will list the 15 or so errors in separate "If Target.value='error' Then" sections.  The only thing I need now is for this code to apply to every sheet of my workbook.  I tried pasting the code in the "This workbook module" but it doesn't seem to work.  Is there something else I need to do?  Thank you for your help!!  -Estebanes

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        
        If Target.Value = "ot/x" Then
            MsgBox "please input a correct value such as ""x/ot""!"
        End If

    Tuesday, September 30, 2014 5:20 PM