Hello,
I have a range of cells which are to be filled by the user. All of them either have a drop down or a formula in them. If a user starts to fill a row, he has to fill up the entire row and cannot leave anything blank. If all cells are left blank and he tries to save it, it will give a msgbox with a warning.
For the part, where all cells are left blank, the following code was written with the help of this forum, just a few seconds ago and it works fine:
'check if all cells are blank
Dim count As Long
count = WorksheetFunction.CountBlank(Range("C6:U19"))
If count = 266 Then 'if above range changes, this number will have to change as well
MsgBox "All cells are empty, please fill data to be saved."
Exit Sub
Else
End If
But, the part after that, where if the user has started entering data in a row, he cannot leave any cell blank in that row: the code I had was working fine until now, not sure what is wrong with it:
'check for empty cells for each entry
Dim rng As Range
On Error Resume Next
Set rng = Intersect(Range("C6:O15").SpecialCells(xlConstants).EntireRow, Range("C6:O15")).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Blanks found" & vbCrLf & rng.Address(0, 0)
Exit Sub
Else
MsgBox "Data Saved Successfully. Please close the workbook. Thank you"
End If
Help please.
I have a range of cells which are to be filled by the user. All of them either have a drop down or a formula in them. If a user starts to fill a row, he has to fill up the entire row and cannot leave anything blank. If all cells are left blank and he tries to save it, it will give a msgbox with a warning.
For the part, where all cells are left blank, the following code was written with the help of this forum, just a few seconds ago and it works fine:
'check if all cells are blank
Dim count As Long
count = WorksheetFunction.CountBlank(Range("C6:U19"))
If count = 266 Then 'if above range changes, this number will have to change as well
MsgBox "All cells are empty, please fill data to be saved."
Exit Sub
Else
End If
But, the part after that, where if the user has started entering data in a row, he cannot leave any cell blank in that row: the code I had was working fine until now, not sure what is wrong with it:
'check for empty cells for each entry
Dim rng As Range
On Error Resume Next
Set rng = Intersect(Range("C6:O15").SpecialCells(xlConstants).EntireRow, Range("C6:O15")).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Blanks found" & vbCrLf & rng.Address(0, 0)
Exit Sub
Else
MsgBox "Data Saved Successfully. Please close the workbook. Thank you"
End If
Help please.