Easy one - Message Box

[ExcelNovice]

I need a macro that will do this:

Generate a pop-message each time the user enter the wrong data in a cell (cell Q4).
Only numbers between 10 and 18 (inclusive) and the word "CAD" should be entered in the cell. I want the message box to pop up if unauthorized data is entered in the cell.

Thanks for your help.

 

[paliman]

In the menu:
data
validate (or something like that)

You can set the conditions for the data to be entered and the text in the message box for bad entries

pali

 

[ExcelNovice]

Thanks Pali, but Validation won't work because the cell have to be able to take both text and values. As far as I know, validation is either text or value.

If someone else have an idea please let me know.

Thanks

 

[kskinne]

insert the following code into your worksheet's object window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 And Target.Row = 4 Then
If Target.Value = "CAD" Then
End
ElseIf Target.Value >= 10 And Target.Value <= 18 Then
End
Else
MsgBox "Wrong Data"
End
End If
Else
End
End If
End Sub

HTH
kevin

 

[Dragracer]

Validation will also work.

You can choose list for your criteria, then enter all your values separated by comas or use a named range with the values listed somewhere in your workbook.

or choose custom for your criteria and enter a if formula. I believe this formula will work for you.

=IF(OR(Q4="CAD",AND(Q4<=18,Q4>=10)),TRUE,FALSE)

may not be elegent but it works

edited to revise formula
This message was edited by Dragracer on 2002-09-23 16:36