Multiple Data Validations In Cell using VBA

[MSBECCA23]

Hello Forum,
I'm trying to code multiple validations in single cell using VBA (Excel 2007).
Currently validation checks to see if the data entered is between 1 and (N number of characters)
ie. between 1 and 30

Here's code:
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:=CStr(arrAttrs(x).AttributeLength)

The problem is: Users are entering a single SPACE in the cell and its passing validation.
I need to add another validation to stop users from entering single SPACE in cell.
The cell has to be either BLANK or contain data within length specified by first validation formula.

How do I go about adding this second validation to range?
I've seen that CUSTOM type option can be used, but not sure how to code it.

Any help would be greatly appreciated.
MsBecca

[Austin Brown]

Hi, since I don't have VBA skill, I can't help you with VBA. But such kind of problems can be resolved easily by another free spreadsheet tool- esCalc, which is similar Excel. By using it, you can just put the validation expression: if(@>30, "too old", @<1, "too young"). There won't be space problem. Besides, this tool can access your data in Excel ,txt, or other datasource.
vanidation.png

[kvsrinivasamurthy]

Try this code
It will not allowany space to enter and max length is 30. But this wil not allow space in any charecter position.

Please Login or Register  to view this content.

[MSBECCA23]

Thanks Austin and kvsrinivasamurthy, I appreciate your quick feedback. I will look into trying both of your suggestions.