warning message

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Back
Top