Data Validation & Messages

I

ibrahimaa

New member
Joined
May 22, 2011
Messages
16
Reaction score
0
Points
0
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).:confused2:
I would appreciate any help. Thank you.
 
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...
 
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. :confused2:Thanks.
 
I think that will involve an event macro... I will leave it to the VBA experts here.
 
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
 

Attachments

  • Example DV message.xls
    22.5 KB · Views: 20
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 ---?
 
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...
 
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.
 

Attachments

  • Example DV message.xls
    38 KB · Views: 21
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.
 
Good morning
Please select “SAT Converted to SAR” and 1 to be the number of heads. You will see the message box; click OK, then highlight both of them (the # of heads and the option” and click delete. You will receive the error # 13. I appreciate your support. Thank you.
 

Attachments

  • Exhibt-6A-Test1.xlsm
    18.3 KB · Views: 17
  • Deleate.jpg
    Deleate.jpg
    91.6 KB · Views: 12
Hi

I see the problem.
When hastily posting a quick example, I hadn't included the test to ensure you didn't have more than 1 cell selected.

If Target.Count > 1 Then Exit Sub

The amended code is
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrange As Range

    Set myrange = Range("B7:B14")
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, myrange) Is Nothing Then
        If Target.Value = "SAT Converted to SAR" Then
            Call MsgBox("You have selected SAT Converted to SAR", vbExclamation, "Warning")
        End If
        
    End If

End Sub
 
You must log in or register to reply here.
Back
Top