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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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(Code_List_Technician))
> Max =
> IF(ISBLANK(Tech_No),MAX(Code_List_Support),MAX(Code_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.
>
|
Similar Threads
