Data validation to force cell population
 

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?

Data validation to force cell population
 

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


wrote in message oups.com...
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?