Data validation routine not working

Question

I have this macro which checks for blanks from A cell to I. If the cells are blank the user isn't able to save. But the problem is that if I dont enter anything in the A cell then save is enabled.

Here's my VBA code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rsave As Range, N As Long
    Dim cell As Range
    With Sheet2
        N = Cells(Rows.Count, "A").End(xlUp).Row
        For i = 1 To N
            Set rsave = Range("A" & i & ":I" & i)
            For Each cell In rsave
                If cell = "" Then
                    Dim missdata
                    missdata = MsgBox("missing data", vbOKOnly, "Missing Data")
                    Cancel = True
                    cell.Select
                    Exit Sub
                End If
            Next cell
        Next i
    End With
End Sub

Have you tried debugging and stepping through the code to see why it enables save? — tospig, Feb 02 '15 at 23:00
[Check this out to improve your coding and where my post below is based](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — L42, Feb 03 '15 at 02:31

L42 · Answer 1 · 2015-02-03T02:45:42.443

I think you should just correctly reference the objects you are working on.
For example, you use With Statement in this line but the next line isn't really referenced to Sheet2.

With Sheet2
    N = Cells(Rows.Count, "A").End(xlUp).Row

To correctly reference the properties and objects, precede it with a dot like:

With Sheet2
    N = .Cells(.Rows.Count, "A").End(xlUp).Row 'notice the dot before Cells

Same goes with this line: Set rsave = Range("A" & i & ":I" & i)
It should be:

Set rsave = .Range("A" & i & ":I" & i) 'notice the dot

Now instead of doing a nested loop, you can do it with one.
Also, reference your last row relative to the entire column. Try below code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rsave As Range, N As Long
    Dim cell As Range
    With Sheet2
        N = .Range("A:I").Find("*", [A1], , , xlByRows, xlPrevious).Row
        Set rsave = .Range("A1:I" & N)
        For Each cell In rsave
            If cell = "" Then
                MsgBox "Missing data.", vbOKOnly, "Missing Data"
                Cancel = True
                cell.Select
                Exit Sub
            End If
        Next cell
    End With
End Sub

You see, cell variable iterate from left to right, up down.
So it checks A1, B1, C1.. etc then A2, B2, C2... HTH.

Data validation routine not working

1 Answers1