I have a cell which I am validating with the data validation
functionality in Excel.

Tech_no, Code_List_Support, and Code_List_Technician are defined names
(the latter 2 are ranges).

The validation is that it must be a whole number with the following min
and max values:

Min =
IF(ISBLANK(Tech_No),MIN(Code_List_Support),MIN(Cod e_List_Technician))
Max =
IF(ISBLANK(Tech_No),MAX(Code_List_Support),MAX(Cod e_List_Technician))

I have the Ignore Blank Values UNCHECKED. However, when I tab over the
cell and leave the cell blank, I do not get an error message. I tried
hitting enter with the same result.

I'd like to make sure that the cell is not blank.

Any suggestions? Thanks.

Connie,

Unchecking "Ignore Blank" prevents the user from leaving cell-edit mode
without entering anything. However it doesn't prevent them from tabbing
through the cell or, for that matter, clearing or deleting the cell
contents. In other words, it only has effect when in cell-edit mode. As
far as I can tell from trying and Googling, there is no Data Validation that
does what you are looking for.

You might try a SelectionChange event in the worksheet module, like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static cell_before_selection As Range

If Not cell_before_selection Is Nothing Then
If cell_before_selection.Address = Range("A2").Address And
IsEmpty(Range("A2")) Then
Application.EnableEvents = False
Range("A2").Select
MsgBox "you must enter something here"
Application.EnableEvents = True
End If
End If
Set cell_before_selection = ActiveCell

End Sub

Of course that doesn't react if they clear a whole range and it includes
your cell, but it's not the ActiveCell, so I guess you could add this to the
worksheet module as well:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo err_handler

Application.EnableEvents = False
If Not Intersect(Target, Range("A2")) Is Nothing Then
If IsEmpty(Range("A2")) Then
Range("A2").Select
MsgBox "you must enter something here"
End If
End If

err_handler:
Application.EnableEvents = True

It kind of begs the question of what if they never enter the cell.

You could also try conditional formatting to draw their attention to the
empty cell, or maybe a formula in a dependent cell that includes a message
if the cell is blank. Also, you could run code before closing the workbook
that checks the cell and insists that they enter something in it.

hth,

Doug


"Connie" wrote in message
oups.com...
I have a cell which I am validating with the data validation
functionality in Excel.

Tech_no, Code_List_Support, and Code_List_Technician are defined names
(the latter 2 are ranges).

The validation is that it must be a whole number with the following min
and max values:

Min =
IF(ISBLANK(Tech_No),MIN(Code_List_Support),MIN(Cod e_List_Technician))
Max =
IF(ISBLANK(Tech_No),MAX(Code_List_Support),MAX(Cod e_List_Technician))

I have the Ignore Blank Values UNCHECKED. However, when I tab over the
cell and leave the cell blank, I do not get an error message. I tried
hitting enter with the same result.

I'd like to make sure that the cell is not blank.

Any suggestions? Thanks.

Lots of great suggestions. Thanks! I will try the suggestions a little
later. I really appreciate your help.

Connie

Doug Glancy wrote:
Connie,

Unchecking "Ignore Blank" prevents the user from leaving cell-edit mode
without entering anything. However it doesn't prevent them from tabbing
through the cell or, for that matter, clearing or deleting the cell
contents. In other words, it only has effect when in cell-edit mode. As
far as I can tell from trying and Googling, there is no Data Validation that
does what you are looking for.

You might try a SelectionChange event in the worksheet module, like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static cell_before_selection As Range

If Not cell_before_selection Is Nothing Then
If cell_before_selection.Address = Range("A2").Address And
IsEmpty(Range("A2")) Then
Application.EnableEvents = False
Range("A2").Select
MsgBox "you must enter something here"
Application.EnableEvents = True
End If
End If
Set cell_before_selection = ActiveCell

End Sub

Of course that doesn't react if they clear a whole range and it includes
your cell, but it's not the ActiveCell, so I guess you could add this to the
worksheet module as well:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo err_handler

Application.EnableEvents = False
If Not Intersect(Target, Range("A2")) Is Nothing Then
If IsEmpty(Range("A2")) Then
Range("A2").Select
MsgBox "you must enter something here"
End If
End If

err_handler:
Application.EnableEvents = True

It kind of begs the question of what if they never enter the cell.

You could also try conditional formatting to draw their attention to the
empty cell, or maybe a formula in a dependent cell that includes a message
if the cell is blank. Also, you could run code before closing the workbook
that checks the cell and insists that they enter something in it.

hth,

Doug


"Connie" wrote in message
oups.com...
I have a cell which I am validating with the data validation
functionality in Excel.

Tech_no, Code_List_Support, and Code_List_Technician are defined names
(the latter 2 are ranges).

The validation is that it must be a whole number with the following min
and max values:

Min =
IF(ISBLANK(Tech_No),MIN(Code_List_Support),MIN(Cod e_List_Technician))
Max =
IF(ISBLANK(Tech_No),MAX(Code_List_Support),MAX(Cod e_List_Technician))

I have the Ignore Blank Values UNCHECKED. However, when I tab over the
cell and leave the cell blank, I do not get an error message. I tried
hitting enter with the same result.

I'd like to make sure that the cell is not blank.

Any suggestions? Thanks.