Depends on how hard you want to force them. To really force them requires
code in two areas: the Worksheet_Deactivate event so that they get forced
back to that sheet when they try to leave it, and in the Workbook_BeforeClose
event so that if they try to close the book with blanks left, they cannot.
The code for both places is very much the same. First here is the code for
the worksheet's _Deactivate() event. To put it in the proper place,
right-click on the sheet's name tab and choose [View Code] from the list.
Then cut and paste this code into the module that's displayed.
Each section of code begins with 4 Const definitions - change those to match
your worksheet name and setup - the rest of the code will take care of itself
after you do that.
Private Sub Worksheet_Deactivate()
'change these Const values to be like your setup
Const theWorksheet = "Sheet1" ' sheet to watch over
Const countryColumn = "A" ' column with countries list
Const listColumn = "B" ' column with list to choose from
Const firstRow = 2 ' first row with a country name in it
Dim testRange As Range
Dim anyCell As Object
Dim cOffset As Long
'assumes validation lists are in column B
'and that your list of countries is in A
'beginning at row 2
'
'calculate offset between columns
cOffset = Range(countryColumn & "1").Column - _
Range(listColumn & "1").Column
Set testRange = Worksheets(theWorksheet). _
Range(listColumn & firstRow & ":" _
& listColumn & _
Range(countryColumn & Rows.Count).End(xlUp).Row)
For Each anyCell In testRange
If IsEmpty(anyCell) And _
Not (IsEmpty(anyCell.Offset(0, cOffset))) Then
MsgBox "You did not enter a value for " _
& anyCell.Offset(0, cOffset), vbOKOnly, _
"Missing Required Data"
Worksheets(theWorksheet).Select 'force back to sheet
anyCell.Select ' show them exactly where!
Set testRange = Nothing
Exit Sub
End If
Next
End Sub
Now we tackle the condition of them trying to close the workbook without
entering all of the needed information. Since I presume you want to be able
to work with it to develop it and save it with those cells empty, we have to
give you a safety valve - first warning them that the data is missing, and
then offering the option to save without entering it anyhow. At least you
know they've been warned.
This code goes into the Workbook_BeforeClose event. To put it there,
right-click on the little Excel icon immediately to the left of the word File
in the Excel menu bar and choose [View Code] from the list that appears. Cut
and paste this into the module that is shown to you and close the VB Editor.
Give it a trial run.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'change these Const values to be like your setup
Const theWorksheet = "Sheet1" ' sheet to watch over
Const countryColumn = "A" ' column with countries list
Const listColumn = "B" ' column with list to choose from
Const firstRow = 2 ' first row with a country name in it
Dim testRange As Range
Dim anyCell As Object
Dim cOffset As Long
'assumes validation lists are in column B
'and that your list of countries is in A
'beginning at row 2
'
'calculate offset between columns
cOffset = Range(countryColumn & "1").Column - _
Range(listColumn & "1").Column
Set testRange = Worksheets(theWorksheet). _
Range(listColumn & firstRow & ":" _
& listColumn & _
Range(countryColumn & Rows.Count).End(xlUp).Row)
For Each anyCell In testRange
If IsEmpty(anyCell) And _
Not (IsEmpty(anyCell.Offset(0, cOffset))) Then
MsgBox "You did not enter a value for " _
& anyCell.Offset(0, cOffset), vbOKOnly, _
"Missing Required Data"
'
'safety valve!
If MsgBox("Do you wish to close " & _
"this workbook without the data?", _
vbYesNo + vbDefaultButton2, "Close Now?") <> vbYes Then
Worksheets(theWorksheet).Select 'force back to sheet
anyCell.Select ' show them exactly where!
Set testRange = Nothing
'added to the Workbook_BeforeClose event
Cancel = True
Exit For '
End If
Exit For ' don't loop through all
End If
Next
End Sub