DV error message with a cell value

S

Soniya

Hi All,


Is it possible to get a cell value to be displayed as an
error meessage in a data validation?

for eg if i have limited the number in A1 to be equal or
multples of B1 how can i get an eror message somethinf
like "amount should be multples of " & B1.value
(the value in B1 may vary)

TIA
Soniya
 
D

Dave Peterson

You could use an event macro to change the data validation error message
whenever you changed B1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("b1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Range("a1")
If .Value Mod Target.Value <> 0 Then
Application.EnableEvents = False
.ClearContents
End If
.Validation.ErrorMessage _
= "Must be a multiple of " & Target.Value & "."
End With

errHandler:
Application.EnableEvents = True
End Sub


Right click on the worksheet tab that should have this behavior. Select View
code and paste this in.

I also looked at the value in A1. If it's not a multiple of the current value
in B1, then it gets cleared. You may not want that.
 
Top