Validations For Multiple Rows in Excel VBA

Hi

I have An Excel Sheet that has validations like numerical check , email validation for the first row. Now I want to extend that validations to 100 rows using VBA. I tried but ididnt get any idea. Here u can see the code for validations for first row ....... Thanks in Advance.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$E$11" Then
If intCheck(Sheets.Item("Calls only (CPS or IDA)").Range("E" & 11), Sheets.Item("Calls only (CPS or IDA)").Range("E" & 11).Value) = False Then
Sheets.Item("Calls only (CPS or IDA)").Range("E" & 11).Select
MsgBox "Only Numericals Allowed for C&W A/C No", vbCritical, "Number Validation"
End If
End IF
End Sub


PLease help me..........

Hello ban and welcome to the forum! :)

Please be sure to read the posting guidlelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

Please can you give us more information about what you mean when you say your sheet has "validations like numerical check , email validation"? This could be quite a few things so I'm not sure what to focus on in my reply.

In terms of VBA - a good way to generate code when you're starting is to use the macro recorder. Look at the "Create a macro" topic in your Excel helpfile. Please note that we don't write code to order here.

Colin

Hello Colin

Thanks for the quick response. Actually in my excel Sheet 1 to 10 rows are hidden. for the 11th row validations are done Ex : in B11 cell we can enter only numeric data. it accepts only numeric data. otherwise it displays a error message box. like that i want to make b12,b13, b14...... b100 als onumeric .

Previously For single row it was ........

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$B$11" Then
If intCheck(Sheets.Item("Calls only (CPS or IDA)").Range("B" & 11), Sheets.Item("Calls only (CPS or IDA)").Range("B" & 11).Value) = False Then
Sheets.Item("Calls only (CPS or IDA)").Range("B" & 11).Select
MsgBox "Only Numericals Allowed for C&W A/C No", vbCritical, "Number Validation"
End If

Now i am trying it like this :

x = Array("A", "B", "C")
For i = 1 To UBound(x)
For column = 11 To 20
If Target.address = "$x(i)$column" Then ' here x(i) =A and column=11
.........................

But the value A11 is not coming into Target.address. please help me

This might get you started:


Dim rngSelectors As Range

Set rngSelectors = Sheet2.Range("c3:c5")

If Not Intersect(Target, rngSelectors) Is Nothing Then

'Code Code Code

End If


TJ

So we're just checking for numbers, right?

Rather than trapping the worksheet_change event, you would be better off using a native tool in Excel called Data Validation. This totally removes any requirement for VBA - keep it simple when possible, eh?

Here's an example how (try it out in a new worksheet):

Select the range B11:B100 in your worksheet.
Go to Data-->Validation
In the allow drop down box, select Custom.
In the formula field type in this formula: =ISNUMBER($B11)


Now try typing things into any of those cells....

There are other options in this menu which you should familiarise yourself with - you can put lower / upper limits on the values, enforce whole numbers only, etc....

Colin

Thank u very much tinyjack. I got the clue....

S right . But along with that numerical validation that cell should also contain another validation that is the number should have 11 digits and it must start with 0. Can we make formula for these 3 validations to be done at once.???????

You didn't say that when I asked you to give more information about the validation.... but yes, you can check all of those in one formula. Take care though, if you are entering numbers with a zero at the start (and they are displayed as such) then Excel is probably considering these as text and not as numbers.

Some initial suggestion:

Check the length ---> Len worksheet function.
Check starts with a zero ---> Left worksheet function
Check is a number ---> ISNUMBER worksheet function (and if text-->number conversion is necessary then VALUE function)
Combining the checks together ---> AND worksheet function.


Colin

AS I am doing all these validations using code i thought if i know doing one validation for 1000 rows , i can do for remaining. Sorry for that. I entered the formula like this ;;;

=ISNUMBER($B11) And Len($B11)=11 AND Left($B11)=0


But its showing The formula is incorrect.. please suggest me..

AS I am doing all these validations using code i thought if i know doing one validation for 1000 rows , i can do for remaining. Sorry for that. I entered the formula like this ;;;

=ISNUMBER($B11) And Len($B11)=11 AND Left($B11)=0


But its showing The formula is incorrect.. please suggest me..

:confused:
Please can you be clear about what you have decided to try to do:

Are you:
1. Trying to validate cell entries using VBA code in the Worksheet_Change event per post#4? OR
2. Trying to enter a data validation formula for a range of cells using VBA (ie. Validation object's formula1 property)? OR
3. Just manually entering a data validation formula along the lines suggested in posts #5 and #8?


Left [VBA or worksheet] functions both have 2 required arguments.
ISNUMBER is a worksheet function.

i am trying to do validations using data validation formula .

What is the difference between VBA Functions and WorkSheet functions?

Okay, option 3 on the list.

The AND worksheet function has this syntax:


Syntax
AND(logical1,logical2, ...)


So when you are using it in a formula, you would use it like this:


=AND(TRUE, TRUE) All arguments are TRUE (TRUE)
=AND(TRUE, FALSE) One argument is FALSE (FALSE)
=AND(2+2=4, 2+3=5) All arguments evaluate to TRUE (TRUE)



And as I mentioned, you need to check the syntax on your LEFT worksheet function.

I hope that helps.

Yes its working fine Thank you....
And the Left Function Syntax is Left(String, len) .i Checked it in Code.
The formula is working for Len and ISNUMBER Functions .
But When i added LEft() in the formula like this

=And(Len($B1)=11 ,ISNUMBER($B1) ,Left($B1,1)=0)

The cells are not accepting the right data also...... : data(01234123412)

Ban: Please do not duplicate threads. It just serves to confuse those trying to help you and makes additional moderating work. If you've posted something and want to add to it you can always use the Edit facility (within 24 hours of original post) to modify. As suggested earlier by Colin, please review the .. to ensure you get the most from being a member here. Thanks.

Good luck with this problem.

Yes its working fine Thank you....
And the Left Function Syntax is Left(String, len) .i Checked it in Code.
The formula is working for Len and ISNUMBER Functions .
But When i added LEft() in the formula like this

=And(Len($B1)=11 ,ISNUMBER($B1) ,Left($B1,1)=0)

The cells are not accepting the right data also...... : data(01234123412)

Okay... good progress, Ban. 2 Problems with it as it stands:

First problem - I hinted at in #8 - is because your cells are formatted as text. You have to modify this argument using the VALUE function:
ISNUMBER($B1) ---> ISNUMBER(VALUE($B1))

Second problem - Left worksheet function returns text, not a number so you have to modify this argument:
Left($B1,1)=0 ---> Left($B1,1)="0"

The overall result is this:

=AND(LEN($B1)=11,ISNUMBER(VALUE($B1)),LEFT($B1,1)="0")


I hope that makes sense.
Colin

thank u very much..............
Previously I Did all these validations using code. It was big hectit. i tried using formula but as it showed wrong formula i gave up..

Thank u very much
Now i can delete lots of code from my application.................
Thank u.........

Muy iSuue has been Solved....