Is it possible to stop a worksheet from printing or saving if a cell has not
been filled in or if a check box has not been checked?

Thank you!

Hi Havenstar,

Assuming the cell is [A1] on "Sheet1", copy the following code into the VBA
module of ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Regards,
KL



"Havenstar" wrote in message
...
Is it possible to stop a worksheet from printing or saving if a cell has
not
been filled in or if a check box has not been checked?

Thank you!

KL,

Sorry I am not very VBA savy can you advise how to add this when I open VBA?

Thanks

"KL" wrote:

Hi Havenstar,

Assuming the cell is [A1] on "Sheet1", copy the following code into the VBA
module of ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Regards,
KL



"Havenstar" wrote in message
...
Is it possible to stop a worksheet from printing or saving if a cell has
not
been filled in or if a check box has not been checked?

Thank you!

yep.

1) With your file open, right-click on the little Excel icon to the left of
the 'File' menu and choose 'View Code'. The VBA Editor will open.
2) Copy and Paste the code I gave to you into the largest window on the
right-hand side.
3) Hit Alt+F11 to return to the sheet.
4) try to print and/or save the file with the cell A1 empty, and then repeat
it with A1 filled with a value.
5) Do not forget that the macros need to be enabled for the code to work
(say Enable when prompted at file opening)

Regards,
KL


"Havenstar" wrote in message
...
KL,

Sorry I am not very VBA savy can you advise how to add this when I open
VBA?

Thanks

"KL" wrote:

Hi Havenstar,

Assuming the cell is [A1] on "Sheet1", copy the following code into the
VBA
module of ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Regards,
KL



"Havenstar" wrote in message
...
Is it possible to stop a worksheet from printing or saving if a cell
has
not
been filled in or if a check box has not been checked?

Thank you!

I keep getting an Error Message stating Compile Error: Sytntax Error and it
Highlights "Private Sub Workbook_BeforePrint(Cancel As Boolean)". Is that
how that works or can I have a message prompt stating 'Form not filled out
complete, please review and fill in all blanks' ?

Thanks
Sandi

"KL" wrote:

yep.

1) With your file open, right-click on the little Excel icon to the left of
the 'File' menu and choose 'View Code'. The VBA Editor will open.
2) Copy and Paste the code I gave to you into the largest window on the
right-hand side.
3) Hit Alt+F11 to return to the sheet.
4) try to print and/or save the file with the cell A1 empty, and then repeat
it with A1 filled with a value.
5) Do not forget that the macros need to be enabled for the code to work
(say Enable when prompted at file opening)

Regards,
KL


"Havenstar" wrote in message
...
KL,

Sorry I am not very VBA savy can you advise how to add this when I open
VBA?

Thanks

"KL" wrote:

Hi Havenstar,

Assuming the cell is [A1] on "Sheet1", copy the following code into the
VBA
module of ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Regards,
KL



"Havenstar" wrote in message
...
Is it possible to stop a worksheet from printing or saving if a cell
has
not
been filled in or if a check box has not been checked?

Thank you!

Hi,

Sounds like line-wrapping issue. Try to copy the following into your module:

Private Sub Workbook_BeforePrint _
(Cancel As Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Private Sub Workbook_BeforeSave(ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

'If you want a message, use this:

Private Sub Workbook_BeforePrint _
(Cancel As Boolean)
If IsEmpty(Sheets("Sheet1").Range("A1")) Then
Cancel = True
MsgBox "Form not filled out complete, " & _
"please review and fill in all blanks"
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(Sheets("Sheet1").Range("A1")) Then
Cancel = True
MsgBox "Form not filled out complete, " & _
"please review and fill in all blanks"
End If
End Sub

Regards,
KL


"Havenstar" wrote in message
...
I keep getting an Error Message stating Compile Error: Sytntax Error and
it
Highlights "Private Sub Workbook_BeforePrint(Cancel As Boolean)". Is that
how that works or can I have a message prompt stating 'Form not filled out
complete, please review and fill in all blanks' ?

Thanks
Sandi

"KL" wrote:

yep.

1) With your file open, right-click on the little Excel icon to the left
of
the 'File' menu and choose 'View Code'. The VBA Editor will open.
2) Copy and Paste the code I gave to you into the largest window on the
right-hand side.
3) Hit Alt+F11 to return to the sheet.
4) try to print and/or save the file with the cell A1 empty, and then
repeat
it with A1 filled with a value.
5) Do not forget that the macros need to be enabled for the code to work
(say Enable when prompted at file opening)

Regards,
KL


"Havenstar" wrote in message
...
KL,

Sorry I am not very VBA savy can you advise how to add this when I open
VBA?

Thanks

"KL" wrote:

Hi Havenstar,

Assuming the cell is [A1] on "Sheet1", copy the following code into
the
VBA
module of ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = IsEmpty(Sheets("Sheet1").Range("A1"))
End Sub

Regards,
KL



"Havenstar" wrote in message
...
Is it possible to stop a worksheet from printing or saving if a cell
has
not
been filled in or if a check box has not been checked?

Thank you!

Thanks for all of your help KL!

"Havenstar" wrote:

Is it possible to stop a worksheet from printing or saving if a cell has not
been filled in or if a check box has not been checked?

Thank you!