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

H

Havenstar

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!
 
K

KL

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
 
H

Havenstar

KL,

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

Thanks

KL said:
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 said:
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!
Click to expand...
Click to expand...
 
K

KL

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 said:
KL,

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

Thanks

KL said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
H

Havenstar

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 said:
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 said:
KL,

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

Thanks

KL said:
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



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!
Click to expand...
Click to expand...
Click to expand...
 
K

KL

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 said:
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 said:
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 said:
KL,

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

Thanks

:

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



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!
Click to expand...
Click to expand...
Click to expand...
 
H

Havenstar

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 said:
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 said:
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 said:
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


KL,

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

Thanks

:

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



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!
Click to expand...
Click to expand...
Click to expand...
 
K

KL

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 said:
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 said:
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 said:
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

:

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


KL,

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

Thanks

:

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



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!
Click to expand...
Click to expand...
Click to expand...
 
H

Havenstar

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 said:
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 said:
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 said:
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


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

:

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


KL,

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

Thanks

:

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



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!
Click to expand...
Click to expand...
Click to expand...
 
K

KL

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 said:
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 said:
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 said:
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

:

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


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

:

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


KL,

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

Thanks

:

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



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!
Click to expand...
Click to expand...
Click to expand...
 
H

Havenstar

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 said:
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 said:
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 said:
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

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

:

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


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

:

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


KL,

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

Thanks

:

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



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!
Click to expand...
Click to expand...
Click to expand...
 
K

KL

No, this is actually instead of the previous.

Regards,
KL


Havenstar said:
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 said:
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 said:
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?

:

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

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

:

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


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

:

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


message
KL,

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

Thanks

:

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



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!
Click to expand...
Click to expand...
Click to expand...
 
Top