CODEWITHSUNDEEP
vbaexcel
Juuri Peeter
Juuri Peeter

Reputation: 131

Data validation macro excel VBA

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

Upvotes: 0

Views: 903

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

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

Upvotes: 1

Related Questions