Data validation to force cell population

[asayther]

I am trying to force users to populate a cell (R20) with information
if cell (B20) equals a value off of a list. My first preference is
that the user is not be able to leave the row (20) without populating
R20; second preference is that user is not able to save workbook
without populating the cell. Any help?

 

[Bernie Deitrick]

asayther,

You could use the change and selection change events to force an entry in cell R20. Copy the code
below, right-click the sheet tab, select "View Code", and paste in the window that appears.

This assumes that your list is stored in a named range myList.

HTH,
Bernie
MS Excel MVP

Dim ForceChange As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myNum As Long

If Intersect(Range("B20,R20"), Target) Is Nothing Then Exit Sub
On Error GoTo NotInList
myNum = Application.Match(Range("B20").Value, Range("myList"), False)
If Range("R20").Value = "" Then
ForceChange = True
Else
ForceChange = False
End If
Exit Sub

NotInList:
ForceChange = False

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not ForceChange Or Target.Address = "$R$20" Then Exit Sub
Application.EnableEvents = False
Range("R20").Select
MsgBox "Please enter a value in cell R20." & Chr(10) & _
"You have a value in B20, and you need one here."
If Range("R20").Value <> "" Then
ForceChange = False
Else
ForceChange = True
End If
Application.EnableEvents = True

End Sub