CODEWITHSUNDEEP
excelvba
Juuri Peeter
Juuri Peeter

Reputation: 131

Data validation macro

I'm creating an Excel sheet which different people are going to add to, so am wondering if there's any simple way to check for the row where user starts writing being filled?

For example, if user starts typing in cell A1, macro checks if the cells are filled on the same row.

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: 1

Views: 7186

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

If by saying "data validation", you mean check for blanks, you can use this:

Sub Test()

Dim wrng As Range

Set wrng = ActiveSheet.UsedRange
MsgBox "The data in a range: '" & wrng.Address & "' are" & IIf(IsValidData(wrng), "", "n't") & " valid"
Set wrng = Nothing

End Sub

Function IsValidData(rng As Range) As Boolean

IsValidData = rng.SpecialCells(xlCellTypeBlanks).Count = 0

End Function

Note, that the UsedRange method returns a range starting from A1 cell. So, you need to add extra code to select a range occupied by the data (skip blanks rows and columns). 

Sub Test()

Dim wrng As Range
Set wrng = GetDataRange()

MsgBox "The data in a range: '" & wrng.Address & "' are" & IIf(IsValidData(wrng), "", "n't") & " valid"

End Sub

Function GetDataRange() As Range

Dim wrng As Range, c As Range, saddr As String
Dim pos As Integer

'get used range
Set wrng = ActiveSheet.UsedRange
'find first non-empty cell in a used range
saddr = ActiveSheet.Range(wrng.End(xlToLeft).Address, wrng.End(xlUp).Address).Address
pos = InStr(1, saddr, ":")
'skip blanks rows and set new range
Set GetDataRange = ActiveSheet.Range(Mid(saddr, pos + 1, Len(saddr) - pos) & ":" & wrng.SpecialCells(xlCellTypeLastCell).Address)

Set wrng = Nothing

End Function

Good luck!

Upvotes: 0

george
george

Reputation: 3221

to expand on the suggested solution, you can do the following. Instead of looping through each cell, your problem can be solved efficiently with two lines of code:

  'get the used range
  Set rsave = Sheet1.Range("a1:i1")
  'Select all blank(not yet filled) cells
  rsave.SpecialCells(xlCellTypeBlanks).Select

This will select all cells which've not been filled in the range a1:i1 of the sheet. Alternatively, you can use some colour to make it more explicit. If it works, don't forget to accept the answer.

Upvotes: 1

Related Questions