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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hide columns based on row values 2
Function Help - Forecast Date past due, adjacent cell is blank. Needto highlight the blank cell. 2
How can we convert OST to PDF file format? 1
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Excel Move or Copy Stopped Working? 0
Printing Worksheet 1
Conditional Formatting on cell with a formula 5
Adding fill to cells only when value is "No" in another cell 1

Back
Top