Validation of cell/answer

M

Martin

I am building a questionnaire in Excel that others will need to fill in. For
one of the cells the respondent's answer should consist of at least 15 words.
Is there a way to build in such a validation of a cell in Excel?
 
L

L. Howard Kittle

Hi Martin,

Try this. Put this formula in any cell away from your questions. I used
H19. Copy the code and paste in the VB Editor of the question sheet. If
A18 has less than 15 words you will get a message box saying so. Of course
change cells to suit, but be sure to refer to them correctly in the formula
and the code.

=LEN(A18)-LEN(SUBSTITUTE(A18," ",""))+1


Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("A18") Then Exit Sub
If Range("H19").Value < 15 Then
MsgBox "You must use 15 or more words" & vbCr & _
" in cell A18"
End If
End Sub

HTH
REgards,
Howard
 
L

L. Howard Kittle

Hi again Martin,

What was I thinking??? Use this formula in Data > Valadation > Settings >
Custom > Formulas.

IF(LEN(A18)-LEN(SUBSTITUTE(A18," ",""))+1<15,"Need 15 words","")

No need for code when the function already exists. You can also use the
Input Message function to remind the user that 15 words is required. It
will appear upon selection of the cell.

HTH
Regards,
Howard
 
M

Martin

Thanks, Howard.
I am not familiar with the VB Editor. Did I understand it correctly that I
should:

Step 1. Tools > Macro > Visual Basic Editor

Step 2. In the VB Editor: Insert > Module

Step 3. Paste in the Module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("A18") Then Exit Sub
If Range("H19").Value < 15 Then
MsgBox "You must use 15 or more words" & vbCr & _
" in cell A18"
End If
End Sub

Step 4. In Excel sheet, paste into cell H19:
=LEN(A18)-LEN(SUBSTITUTE(A18," ",""))+1

This should give a message when there are less than 15 words in cell A18.

Is this correct?
 
L

L. Howard Kittle

Not quite. If you want to use this method you would copy the code and then
right click on the sheet tab and click view code. Past in the large white
area.

The code is checking to see if A18 is the Target and if not exit the sub.
If it is target then it looks at cell H19 (which has the formula that counts
the number of words in A18) to see if it is less than 15. If less than 15
it produces a message box.

See my second solution, you can use a modified version of the formula and
Data > Validation etc which will do the job also.

HTH
Regards
 
M

Martin

Thanks! It worked

L. Howard Kittle said:
Hi again Martin,

What was I thinking??? Use this formula in Data > Valadation > Settings >
Custom > Formulas.

IF(LEN(A18)-LEN(SUBSTITUTE(A18," ",""))+1<15,"Need 15 words","")

No need for code when the function already exists. You can also use the
Input Message function to remind the user that 15 words is required. It
will appear upon selection of the cell.

HTH
Regards,
Howard
Click to expand...
 
Top