If a cell is blank do no let a user print or save?
 

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!

Hi,

I have tried the second option with the message included and now I get
Run-Teim Error '9': Subscript out of range and it highlights "If
IsEmpty(Sheets("Sheet1").Range("F13")) Then"

Thanks

"KL" wrote:

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!

That is, almost for sure, because you don't have a sheet called "Sheet1" (or
not exactly) - please how exactly your sheet's name is spelled.

KL

"Havenstar" wrote in message
...
Hi,

I have tried the second option with the message included and now I get
Run-Teim Error '9': Subscript out of range and it highlights "If
IsEmpty(Sheets("Sheet1").Range("F13")) Then"

Thanks

"KL" wrote:

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!

Sweet! You are so awesome! If I want to add that same thing for the same
sheet but for a Check Box I have created - can I do that if I have a cell
link that is "False" too?

"KL" wrote:

That is, almost for sure, because you don't have a sheet called "Sheet1" (or
not exactly) - please how exactly your sheet's name is spelled.

KL

"Havenstar" wrote in message
...
Hi,

I have tried the second option with the message included and now I get
Run-Teim Error '9': Subscript out of range and it highlights "If
IsEmpty(Sheets("Sheet1").Range("F13")) Then"

Thanks

"KL" wrote:

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!

Hi Havenstar,

Yes you can. Actually, if the linked cell gets the values TRUE or FALSE from
the checkbox, you can use its value directly in your code, e.g.:

Private Sub Workbook_BeforePrint _
(Cancel As Boolean)
If Not 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 Not 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
...
Sweet! You are so awesome! If I want to add that same thing for the same
sheet but for a Check Box I have created - can I do that if I have a cell
link that is "False" too?

"KL" wrote:

That is, almost for sure, because you don't have a sheet called "Sheet1"
(or
not exactly) - please how exactly your sheet's name is spelled.

KL

"Havenstar" wrote in message
...
Hi,

I have tried the second option with the message included and now I get
Run-Teim Error '9': Subscript out of range and it highlights "If
IsEmpty(Sheets("Sheet1").Range("F13")) Then"

Thanks

"KL" wrote:

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!

Good Morning KL,

Am I to put this right after the 1st code you gave me? I tried that and I
got a Compile Error: Ambiguous name detected: Workbook_BeforePrint and it
highlighted "Private Sub Workbook_BeforePrint _
(Cancel As Boolean"

Thanks
Havenstar

"KL" wrote:

Hi Havenstar,

Yes you can. Actually, if the linked cell gets the values TRUE or FALSE from
the checkbox, you can use its value directly in your code, e.g.:

Private Sub Workbook_BeforePrint _
(Cancel As Boolean)
If Not 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 Not 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
...
Sweet! You are so awesome! If I want to add that same thing for the same
sheet but for a Check Box I have created - can I do that if I have a cell
link that is "False" too?

"KL" wrote:

That is, almost for sure, because you don't have a sheet called "Sheet1"
(or
not exactly) - please how exactly your sheet's name is spelled.

KL

"Havenstar" wrote in message
...
Hi,

I have tried the second option with the message included and now I get
Run-Teim Error '9': Subscript out of range and it highlights "If
IsEmpty(Sheets("Sheet1").Range("F13")) Then"

Thanks

"KL" wrote:

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!

No, this is actually instead of the previous.

Regards,
KL


"Havenstar" wrote in message
...
Good Morning KL,

Am I to put this right after the 1st code you gave me? I tried that and I
got a Compile Error: Ambiguous name detected: Workbook_BeforePrint and
it
highlighted "Private Sub Workbook_BeforePrint _
(Cancel As Boolean"

Thanks
Havenstar

"KL" wrote:

Hi Havenstar,

Yes you can. Actually, if the linked cell gets the values TRUE or FALSE
from
the checkbox, you can use its value directly in your code, e.g.:

Private Sub Workbook_BeforePrint _
(Cancel As Boolean)
If Not 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 Not 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
...
Sweet! You are so awesome! If I want to add that same thing for the
same
sheet but for a Check Box I have created - can I do that if I have a
cell
link that is "False" too?

"KL" wrote:

That is, almost for sure, because you don't have a sheet called
"Sheet1"
(or
not exactly) - please how exactly your sheet's name is spelled.

KL

"Havenstar" wrote in message
...
Hi,

I have tried the second option with the message included and now I
get
Run-Teim Error '9': Subscript out of range and it highlights "If
IsEmpty(Sheets("Sheet1").Range("F13")) Then"

Thanks

"KL" wrote:

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!