....what i mean is, how do i prevent a user from just leaving the cell
empty.

i have a list of countries in column A, and in column B i have created
a data validation list (of prices). but users can simply leave column
B empty if they wish to - how do i force them to 'pick' from the list?
hope this makes sense. thanks


column a column b (drop down list to choose from)
belgium
denmark
uk
etc

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


"showsomeidnow" wrote:

....what i mean is, how do i prevent a user from just leaving the cell
empty.

i have a list of countries in column A, and in column B i have created
a data validation list (of prices). but users can simply leave column
B empty if they wish to - how do i force them to 'pick' from the list?
hope this makes sense. thanks


column a column b (drop down list to choose from)
belgium
denmark
uk
etc

thanks all for your help - i now know it's not a simple 'tick box'
option available in excel. appreciate all the advice.

No, it's certainly not. Basically even if you set data validation to not
accept blank entries, as long as you never click in one of those lists, Excel
does not check to see that you've entered information into those. So a
programming solution is pretty much the only way to go.

You could even dispense with the safety valve in the _BeforeClose() event
with a little trickery - I've done this before in similar situations. We
could put code in there that looks for a particular word or phrase in a
specific cell on a specific sheet and if that is there, then go ahead and
close the workbook without checking to see if the lists have been populated -
and erase the word/phrase in the process. That allows you to set up a
"clean" sheet and save it out for distribution and lets you do away with the
safety valve option that they could use to close the workbook without
completing it also.

"showsomeidnow" wrote:

thanks all for your help - i now know it's not a simple 'tick box'
option available in excel. appreciate all the advice.

JLatham, thanks for the useful info also.