Data validation in VBA

[AC]

I have a 3 column by 5 row grid and I am using the code below to put numbers in it, is there any way I can check and make sure the numbers in the first column, column C, are between 1-20 the second column between 21-40 and the third between 41-60, what I was thinking is that if a number is put in wrong the code would stop you with a message box to let you know the number was wrong and then when you ok the message box you would be back in the cell with the wrong number so you could put in a different number.
Using excel 2003
Thanks

Option Explicit
Sub PutInNumbers()
Dim c As Range
Dim i As Integer
Dim j As Integer
Dim val1 As String

Range("C5").Select

Set c = ActiveCell
 
For i = 0 To 2
For j = 0 To 4
 
c.Offset(j, i).Select

val1 = InputBox("Please enter number", "Enter Number")

If val1 = "" Then
Exit Sub

Else

c.Offset(j, i).Value = val1
End If
Next j
Next i
End Sub

 

[AlphaFrog]

Sub PutInNumbers()

    Dim c As Range
    Dim i As Integer
    Dim j As Integer
    Dim val1 As Variant
    
    Range("C5:E9").Select
    Range("C5").Activate
    
    Set c = ActiveCell
     
    For i = 0 To 2
        For j = 0 To 4
        
            c.Offset(j, i).Activate
            
            Do
                val1 = Application.InputBox("Please enter number", "Enter Number", Type:=1)
                If val1 = False Then Exit Sub 'User canceled
        
                If val1 < i * 20 + 1 Or val1 > i * 20 + 20 Then
                    val1 = 0
                    MsgBox "Please enter a value between " & i * 20 + 1 & " and " & i * 20 + 20, vbCritical, "Invalid Entry"
                End If
                
            Loop Until val1 > 0
            
            c.Offset(j, i).Value = val1
            
        Next j
    Next i
    
End Sub

 

[AC]

Thanks, that works

 

[al_b_cnu]

Hi,

You dont actually need VBA for this - select the cells C5:E9 and Data / Validation se the custom formula

=AND(C5>=((COLUMN()-3)*20)+1,C5<=(COLUMN()-2)*20)

 

[AC]

I don't think Data Validation works if you are putting the data in with VBA, or is there a way around this?

 

[al_b_cnu]

True, I was just pointing out that you can avoid VBA totally if you wished, thus avoiding the cumbersome enabling of macros, and the user having to know (s)he has to run a macro in order to enter data into the table