Excel - No empty cell

[teebird]

How can I stop a User from leaving a cell (in spreadsheet) empty. I have tried Data Validation but the user needs to click in the cell for this to work.

Is there something like when the cell loses focus, an error message will pop up saying that the cell is empty and must have a value.

I have tried this If statement but it is not working.

ws.Cells(orow, "A").Value = Range("Input!B5")
If Len(Range("B5").Value) = 0 Then
MsgBox "Please add the Patient's HRN!", vbOKOnly, "HRN Error"
Range("B5").Select
End If
Exit Sub

ws.Cells(orow, "B").Value = Range("Input!B6")
If Len(Range("B6").Value) = 0 Then
MsgBox "Please add the Patient's Name!", vbOKOnly, "Name Error"
Range("B6").Select
End If
Exit Sub

Thanks for any help.

 

[SkipVought]

You want to use the Worksheet_SelectionChange event.

Right-click the Sheet Tab and select View Code This is a Sheet Object Code Module (as opposed to a Module)

In the upper-lh corner of the code sheet select Wosksheet from the dropdown.

In the upper-rh corner of the code sheet select SelectionChange from the dropdown.

Here's the logic.

Store the PREVIOUS cell selection address, then store the CURRENT cell selection address.

If the PREVIOUS cell address is the address of interest AND the PREVIOUS cell address value is empty, then Select the PREVIOUS cell and flash a message. I have NAMED 2 ranges on a hidden sheet, Prev & This

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 'user must enter a value in A1
   [Prev] = [This]
   [This] = Target.Address
   With Range([Prev])
      If [Prev] = [A1].Address And .Value = "" Then
         .Select
         MsgBox "Enter a value, soupy!"
      End If
   End With
End Sub

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[teebird]

Excellent thanks for that - but it has the Run-time Error 424 Object Required.

this problem is this line.
[Prev] = [This]

Do I have to define these values?

 

[SkipVought]

They are named ranges on the worksheet.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}