DV error message with a cell value

  • Thread starter Thread starter Soniya
  • Start date Start date
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
 
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.
 
Back
Top