require cell completion in a form?

[jtdarby]

I have a very simple form in Excel. I'm not sure if it matters, but
the machine I'm on uses Excel 2002, and most of the users are on Excel
2000. The first 2 questions on my form require that the user select
from a list of names, and then select their relationship to that
person. Then the users answer a series of questions about this
person's job performance.

I'm using data validation to manage the two drop-down lists - the
names and the relationships. However, about a third of my users end
up leaving one or both of these cells blank. I have a comment box set
up as a reminder/instructions when they select the cell. I have
Ignore Blank unselected in the data validation menu, and a warning set
up there to show the error alert if they enter invalid data. However,
unless they type a space or a name that's not on the list, they don't
seem to see the reminder. Leaving it blank entirely doesn't appear to
trigger the warning.

Is there any way to *require* that the users complete these two cells?
Thanks very much for any advice.

 

[Debra Dalgleish]

You could use conditional formatting to hide the questions until the
name and relationship are selected.

For example, with Name dropdown in cell D3, and Relationship in D5 --

Select the cells that contain the questions
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the text box, type: =OR($D$3="",$D$5="")
Click the Format button

 

[Trevor]

I have experienced the same thing. Invalid data in a cell does not trigger
any sort of error from Excel if that invalid data was there when the
validation rules were assigned. Someone has to actively edit the cell
before Excel runs through its verifications.

One thing that might work is to create a VBA macro which rund when the file
is first opened, or when the user clicks a "Start" button, or froma menu
item. That macro can pop up 2 dialog boxes, in succession, asking the user
to answer the 2 questions.

I've never put drop down lists in pop-up windows, but I'll bet it's
possible.

 

[Dave Peterson]

I bet you could use some kind of worksheet event that won't allow them to select
a different cell unless the 2 cells are filled in.

But I like to just use two neighbor cells that are bolded bright red with a
formula like:

=if(trim(a1)<>"","","Please enter a value in A1!"

If you want to try a macro approach, right click on the worksheet tab that
should have this behavior and select view code. Paste this into the code
window:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("B2,B4")

On Error GoTo errHandler:

For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
Application.EnableEvents = False
myCell.Select
MsgBox "Please type something in: " & myCell.Address(0, 0)
Exit For
End If
Next myCell

errHandler:
Application.EnableEvents = True

End Sub

Change B2,B4 to the addresses of the cells you want to check.

But if the user disables events or disables macros, then this won't work.

And if the user were just arrowing down to find something required, then this
could get you yelled at!

====
Another alternative:

Maybe you could "kill" a few calculations so that the user has to complete the
cells:

=if(or(trim(b2)="",trim(b4)=""),"You gotta type something!",yourformulahere)

add enough of these and the results will be useless until they do the data
entry.