Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Data Validation & Messages

  1. #1

    Data Validation & Messages



    Register for a FREE account, and/
    or Log in to avoid these ads!

    In the data validation, I have selected a list, identified the source rang that refer to a list of 6 options.
    I want an information message appear if the user selected option # 3 only.
    Also, I want to know any command that can create a message in xls (without using data validation).
    I would appreciate any help. Thank you.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,161
    Articles
    0
    Not sure if it's what you want... but you can easily use an IF() formula to create a message in a cell based on conditions you set.

    e.g. =IF(A1="Option 3","This is my pop-up message","")

    this will only show the message in the cell you put it in if Option 3 is selected from drop down in A1.

    You can Conditionally format the cell based on the condition too.. if you want to add colour, bolding, etc...

    Easy and effective.

    You can even use VLOOKUP to help you pick a different message from a table based on each option picked...


  3. #3
    Thanks for your reply. However, I am looking for a message to be generated in a box similar to what we can see in the data validation if a wrong data was entered. In this situation the selection is valid and it is within the list range but I just want to show warning to the user in a box. I think even data validation\list should provide such feature but I do not know how. I appreciate any support. Thanks.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,161
    Articles
    0
    I think that will involve an event macro... I will leave it to the VBA experts here.


  5. #5
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    I think the following will do what you want.
    Alter the event code to display the message you want, and alter my range to cover the range of cells which have your DV.

    see attached file

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim myrange As Range
    
        Set myrange = Range("A1:A20")
        If Not Intersect(Target, myrange) Is Nothing Then
            If Target.Value = "Option 3" Then
                Call MsgBox("You have selected Option 3", vbExclamation, Application.Name)
            End If
        End If
    
    End Sub
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  6. #6
    Thanks a lot for the great help. Can you please tell me how can I change the message properties such as the message title, color, font ---?

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    You can change the message header by substituting Application.Name with "My Text". (The quotes are required.)

    To change the color and font though, you're going to need to build a custom userform. That takes quite a bit more work as you'll need to control all aspects of the form...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8

    Smile

    I appreciate your help. Thank you.

  9. #9
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    Without getting into very advanced coding with API calls, you cannot change the colour or font of a Messagebox.

    You could display a user form of your design, with a message in whatever font, size, colour, background etc. that you want, which would disappear as soon as the user clicked the X at the top of the form.

    A "quick and nasty" example is attached to get you started - don't have more time for it right now.
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  10. #10
    Good afternoon,
    The message was working fine until I copied it to Excel Workbook where I keep all my original formulas. Based on the message I that received, I have re-saved the file in Excel Macro-Enabled Workbook which accepted the message code.
    However, I have started to receive a new error message that is
    Run-time error ‘13’”
    Type mismatch
    Then the Debug highlighted in yellow the following part:
    If Target.Value = "SAT Converted to SAR" Then
    I have changed the format of the cells to general or text but I still receive the same error.

    Maybe I need to change Target.Value to other thing. I appreciate your support. Thank you.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •