disable printing due to empty cells

[superchew]

is there vba to disable printing if all cells within a range and empty?

[Arkadi]

In the VBA editor, double-click "This Workbook" and past this code there. In this code the range of cells that can't be empty is A5 to A10, but you can change that as needed. Also, the code assumes Sheet1 is the sheet with the cells that can't be empty, so change that as needed too.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim myrange As Range
Dim mycell As Range

Cancel = True
Set myrange = Worksheets("Sheet1").Range("A5:A10")
For Each mycell In myrange
    If mycell.Value <> "" Then Cancel = False
Next mycell

End Sub

[superchew]

with this do all cell in the range have to have data? I need it to work if only 1 has data but not run if no cells in the range have data?

[Arkadi]

This code would not print if ALL cells in the range are blank. If even just one cell in the range has data, then it will print.

[superchew]

how would i add a msg box to let someone know can't print until info is entered.

[Arkadi]

Use this ammended code, just change the part in the quotes (the msgbox line) to say what you want:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim myrange As Range
Dim mycell As Range

Cancel = True
Set myrange = Worksheets("Sheet1").Range("A5:A10")
For Each mycell In myrange
    If mycell.Value <> "" Then Cancel = False
Next mycell
If Cancel = True Then MsgBox ("Can't print until all cells are filled")
End Sub