PDA

View Full Version : Data Validation & Messages



ibrahimaa
2011-05-26, 12:25 PM
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.

NBVC
2011-05-26, 02:50 PM
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
2011-05-26, 06:30 PM
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.

NBVC
2011-05-26, 06:51 PM
I think that will involve an event macro... I will leave it to the VBA experts here.

Roger Govier
2011-05-27, 01:21 AM
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
2011-05-27, 05:22 PM
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
2011-05-27, 11:44 PM
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
2011-05-28, 05:14 AM
I appreciate your help. Thank you.

Roger Govier
2011-05-28, 10:59 AM
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
2011-05-28, 01:14 PM
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.

Roger Govier
2011-05-29, 12:06 AM
Hi

Works fine for me.
Are you sure that what you are testing for in code is EXACTLY the same as the entry you have in your cell in the workbook?

Can you upload a copy of the workbook for us to have a look at?

ibrahimaa
2011-05-29, 05:17 AM
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.

Roger Govier
2011-05-29, 09:47 AM
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


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