Creating a Required Field

[Bob]

I am creating a Questionnaire with the following columns:

Column A – contains the list of questions.
Column B – contains Data Validation dropdown boxes with “Yes†and “Noâ€
choices.
Column C – used for inputting an Explanation.

For each question, the following business rule must be satisfied before
allowing the user to move to any other question:

If a user responds “Yes†in column B, the active cell automatically becomes
the corresponding cell in column C, and the user MUST provide an explanation
(>=20 characters in length) before being allowed to exit the cell.

Being a VBA novice, I do not have a clue how to program this. Any help
would be greatly appreciated.

Thanks,
Bob

 

[Luke M]

'This might work. Right click on your sheet tab, view code, paste all of
this in:
'=================
Dim xNeed As Boolean
Dim ExplainCell As Range


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not (Intersect(Target, Range("B:B")) Is Nothing) Then
If Target.Count > 1 Then Exit Sub
If UCase(Target.Value) = "YES" Then
xNeed = True
Set ExplainCell = Target.Offset(0, 1)
ExplainCell.Select

GoTo GetOut:
End If
ElseIf Not (Intersect(Target, Range("C:C")) Is Nothing) Then
If UCase(Target.Offset(0, -1).Value) = "YES" And _
Len(Target.Text) < 20 Then
MsgBox "Please provide a longer explanation"
Target.Select
GoTo GetOut:
End If
xNeed = False
End If
GetOut:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If xNeed And _
Len(ExplainCell.Text) < 20 Then
ExplainCell.Select
MsgBox "Please provide an explanation"
End If
Application.EnableEvents = True
End Sub

'======================

 

[Bob]

Hi Luke,
Thanks for your help. I really appreciate it.
Unfortunately, as soon as I moved my cursor to column B, I received the
following error message:

Run-time error 91
Object variable or With block variable not set.

I'm not sure how to fix this problem.
Also, for whatever its worth, row 1 contains my column headings. The
questions are contained in rows 2 - 10.

Regards,
Bob