Data Validation & Messages

[ibrahimaa]

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.

[NBVC]

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...

[ibrahimaa]

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.

[NBVC]

I think that will involve an event macro... I will leave it to the VBA experts here.

[Roger Govier]

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

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

[ibrahimaa]

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 ---?

[Ken Puls]

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...

[ibrahimaa]

I appreciate your help. Thank you.

[Roger Govier]

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.

[ibrahimaa]

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.