Validate data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ricoy-Chicago - 13 Oct 2006 16:20 GMT

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

Reply to this Message

Earl Kiosterud - 13 Oct 2006 16:53 GMT

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.

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

>I have a range, let's say: (A1:D4), where users can enter a value on any
>the
[quoted text clipped - 9 lines]
>
> Thanx

Reply to this Message

Earl Kiosterud - 13 Oct 2006 16:58 GMT

Chicago,

Oops. That should be Data - Validation - Custom. Not "Formula is."

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Chicago,
>
[quoted text clipped - 19 lines]
>>
>> Thanx

Reply to this Message

Earl Kiosterud - 13 Oct 2006 17:02 GMT

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.

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Chicago,
>
[quoted text clipped - 19 lines]
>>
>> Thanx

Reply to this Message

Dave Peterson - 13 Oct 2006 16:56 GMT

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

> 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
[quoted text clipped - 6 lines]
>
> Thanx

Signature

Dave Peterson

Reply to this Message

Ricoy-Chicago - 13 Oct 2006 17:24 GMT

Thank you Dave, it is just right!

> Option Explicit
> Sub testme()
[quoted text clipped - 36 lines]
> >
> > Thanx

Reply to this Message

Rate this thread: