Stop save if table has empty cells

[dave843]

Hi,

I have a spreadsheet with a table in it and I want to make sure all cells have a value before the spreadsheet is saved. The code below seems to work if there is an empty cell within the table, however I get an error when there a no empty cells. Any help will be greatly appreciated!

Thanks
Dave


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


If ActiveSheet.ListObjects("HCIIP_Table").DataBodyRange.SpecialCells(xlCellTypeBlanks).Count > 0 Then
MsgBox "There are empty cells within the table."
Cancel = True
End If
End Sub

[paulked]

Hi, welcome to the forum!


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng As Range, blnk As Long
Set rng = ActiveSheet.ListObjects("HCIIP_Table").DataBodyRange
blnk = WorksheetFunction.CountBlank(rng)
If blnk > 0 Then
MsgBox "There are empty cells within the table."
Cancel = True
End If
End Sub

[SamT]

You didn't say what error gets raised...

Yet another method, 'cuz maybe that sheet isn't active when saving


With Sheets("SheetName").ListObjects("HCIIP_Table").DataBodyRange
If WorksheetFunction.CountA(.Cells) <> .Cells.Count Then
Blah blah
End If
End With

[dave843]

Thanks guys! Both those solutions work great. SamT - good point! I'll remember to actually include the error next time. Thanks again :)