Validate data

R

Ricoy-Chicago

I have a range, let's say: (A1:D4), where users can enter a value on any the
cells in this range. However, before any work is done based on the data in
this range, I have to make sure that any the values entered are greater than
zero but less or equal than a certain value. Nulls are OK. If this happens a
msgbox will pop up.

how can I create a macro that will do this for me? I looked through the
threads but I cannot find anything similar.

Thanx
 
E

Earl Kiosterud

Chicago,

How about data validation.

Select A1:D4, with A1 the active (white) cell.
Data - Validation - Formula is: =AND(A1>0, A1<= $G$1)

G1 could be a named cell instead. You can use the Input Message and Error
Alert tabs to customize this further.
 
D

Dave Peterson

Option Explicit
Sub testme()
Dim myRng As Range
Dim OkToContinue As Boolean
Dim myNum As Double

myNum = 3.14159
Set myRng = ActiveSheet.Range("a1:d4")

OkToContinue = True
If Application.Count(myRng) = 0 Then
'all non-numeric, should be ok??
Else
If Application.Min(myRng) <= 0 Then
OkToContinue = False
Else
If Application.Max(myRng) > myNum Then
OkToContinue = False
End If
End If
End If

If OkToContinue = False Then
MsgBox "Please enter values >0 and less than " & myNum
Else
'do the work
End If

End Sub
 
E

Earl Kiosterud

Chicago,

I just realized I might have not provided what you want. My solution
prevents the undesired values from ever being allowed into the cells.
Dave's solution doesn't prevent this, but checks them at macro run time,
which is what you actually asked for.
 
R

Ricoy-Chicago

Thank you Dave, it is just right!

Dave Peterson said:
Option Explicit
Sub testme()
Dim myRng As Range
Dim OkToContinue As Boolean
Dim myNum As Double

myNum = 3.14159
Set myRng = ActiveSheet.Range("a1:d4")

OkToContinue = True
If Application.Count(myRng) = 0 Then
'all non-numeric, should be ok??
Else
If Application.Min(myRng) <= 0 Then
OkToContinue = False
Else
If Application.Max(myRng) > myNum Then
OkToContinue = False
End If
End If
End If

If OkToContinue = False Then
MsgBox "Please enter values >0 and less than " & myNum
Else
'do the work
End If

End Sub
Click to expand...
 
Top