Prevent blank cells in excel

[miki12]

Hi to all

I need some help. I have a macro that prevents saving Workbook with blank (empty) cells in user defined range:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myRanges As Variant
Dim iCtr As Long

myRanges = Array(Me.Worksheets("Sheet1").Range("a1:a3,b7,c9"), _
Me.Worksheets("Sheet2").Range("c1:c2"), _
Me.Worksheets("Sheet3").Range("x1"))

For iCtr = LBound(myRanges) To UBound(myRanges)
With myRanges(iCtr)
If .Cells.Count <> Application.CountA(.Cells) Then
MsgBox "Please fill in all the cells in: " _
& .Parent.Name & vbLf & .Address(0, 0)
Cancel = True
Exit For
End If
End With
Next iCtr
End Sub


QUESTION: How can this macro be modified to alert user immediately when he/she makes a mistake and enters blank data to cell (immediately when he/she accidentally makes empty cell), and not when he tries to save the workbook and the damage to calculations is already made as upper macro do?


Miki

[rcm]

I would suggest to validate the data for the cells in a way that blank is not an option. An warning will pop up if the value entered is not within the range specified.

[miki12]

Cell data can not be validated for empty (blank) cells. Blank cells are simply ignored that way. The only way to do this validation is through macro.