Data validation macro excel VBA

Question

im trying to create a macro which checks if user has filled his row. For example, if user starts typing in cell A1, macro checks if the cells are filled on the same row if theres a blank user cant close excel before all blanks are filled.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rsave As Range
Dim cell As Range
Set rsave = Sheet1.Range("a1:i1")
For Each cell In rsave
If cell = "" Then
    Dim missdata
    missdata = MsgBox("missing data", vbOKOnly, "Missing Data")
    Cancel = True
    cell.Select  
    Exit For
   End If
  Next cell
End Sub

This seems to do what you want.....What is the issue??

Gary's Student
– Gary's Student

2015-02-01 19:48:10 +00:00
Commented Feb 1, 2015 at 19:48 — Gary's Student
– Gary's Student, Commented Feb 1, 2015 at 19:48

Gary's Student · Accepted Answer · 2015-02-01 20:37:39Z

1

To perform this for more than one row, use:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rsave As Range, N As Long
Dim cell As Range
With Sheet1
    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

edited Feb 1, 2015 at 20:37

answered Feb 1, 2015 at 19:56

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juuri Peeter Over a year ago

Thanks man,dots before Cells(Rows.Count, "A") and .Range("A" & i & ":I" & i) has to be removed then works perfectly!

Collectives™ on Stack Overflow

Data validation macro excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related