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

'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

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

--
Best Regards,

Luke M
"Bob" <(E-Mail Removed)> wrote in message
news:5875AC30-BC3D-42B7-B880-(E-Mail Removed)...
>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
>

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


"Luke M" wrote:

> '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
>
> '======================
>
> --
> Best Regards,
>
> Luke M
> "Bob" <(E-Mail Removed)> wrote in message
> news:5875AC30-BC3D-42B7-B880-(E-Mail Removed)...
> >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
> >
>
>
> .
>