warning message

[Guest]

HI, I want to display a warning message in column J whenever the number
exceeds 2.

At the moment I got something like this....

Dim ErrorMSG As String

If Application.CountIf(Worksheets("Sheet1").Columns("J"), ">2") Then

ErrorMSG = MsgBox("Warning.")

End If

Do i need to put this in the public sub function()...I am not too sure about
the full code .

thanks

 

[Guest]

If you mean that you want to warn someone when they enter a value greater
than two into a cell in column J, then put this code into the worksheet's
code module (right-click sheet tab name and choose [View Code] from the list)

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(Target, Range("J:J")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub ' can't evaluate multiple cells
End If
If Target > 2 Then
MsgBox "You have entered a value greater than 2.", vbOKOnly, "Value Error"
End If

End Sub

if you mean to warn them when they have made entries in more than two cells
in column J then this will work, goes into the worksheet code module also:

Private Sub Worksheet_Change(ByVal Target As Range)
Const colToTest = "J:J"
If Application.Intersect(Target, Range(colToTest)) Is Nothing Then
Exit Sub
End If
If Application.WorksheetFunction.CountA(ActiveSheet.Range(colToTest)) > 2 Then
MsgBox "You have entered values in more than two cells in column J.", _
vbOKOnly, "Data Entry Error"
End If

End Sub

 

[Guest]

Allen
I try to keep it simple, is their no way you can just use Data Validation?
It has the error handler included. It will write the code for you.

Good Luck