message flash if sheet incomplete before printing
 

I have a sheet that requires certain fields to be filled in before printing.
Is it possible to have a message pop up if any of the fields have not been
completed when I click on the print button.

Thanks in advance of any help.

John

message flash if sheet incomplete before printing
 

Thanks for the code, however I might have misled you, the fields that need to
be completed, already have some data in them and these need to be overtyped.
I presume that your code checks to see if the cells are blank, but can I get
the message to pop up if some cells are already populated with speciifc data.
eg. Cell C1 = "Enter Reg. No.", cell C2 = "Enter Colour Code" etc.

Thanks again for any help

"Norman Jones" wrote:

Hi John,

Try:

'=============
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range

Set SH = Me.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("A1:C1") '<<==== CHANGE

If rng.Cells.Count Application.CountA(rng) Then
Cancel = True
MsgBox " All the cells in " & rng.Address(0, 0) _
& " on sheet " '& SH.Name _
& " need to be completed!"
End If

End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"John Davies" wrote in message
...
I have a sheet that requires certain fields to be filled in before
printing.
Is it possible to have a message pop up if any of the fields have not been
completed when I click on the print button.

Thanks in advance of any help.

John

message flash if sheet incomplete before printing
 

Hi John,

Try something like:

'=============
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim arr As Variant
Dim i As Long

Set SH = Me.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("C1:C3") '<<==== CHANGE

arr = Array("Enter Reg. No", "Enter Colour Code", _
"Enter something else") '<<==== CHANGE

For Each rCell In rng.Cells
i = i + 1
If rCell.Value = arr(i - 1) Or rCell.Value = "" Then
MsgBox " All the cells in " & rng.Address(0, 0) _
& " on sheet " & SH.Name _
& " need to be completed!"
Cancel = True
Exit For
i = i + 1
End If
Next rCell

End Sub
'<<=============


---
Regards,
Norman


"John Davies" wrote in message
...
Thanks for the code, however I might have misled you, the fields that need
to
be completed, already have some data in them and these need to be
overtyped.
I presume that your code checks to see if the cells are blank, but can I
get
the message to pop up if some cells are already populated with speciifc
data.
eg. Cell C1 = "Enter Reg. No.", cell C2 = "Enter Colour Code" etc.

Thanks again for any help

"Norman Jones" wrote:

Hi John,

Try:

'=============
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range

Set SH = Me.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("A1:C1") '<<==== CHANGE

If rng.Cells.Count Application.CountA(rng) Then
Cancel = True
MsgBox " All the cells in " & rng.Address(0, 0) _
& " on sheet " '& SH.Name _
& " need to be completed!"
End If

End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"John Davies" wrote in message
...
I have a sheet that requires certain fields to be filled in before
printing.
Is it possible to have a message pop up if any of the fields have not
been
completed when I click on the print button.

Thanks in advance of any help.

John

message flash if sheet incomplete before printing
 

Thanks Norman, that works fine, however to be a pain is it possible to
include code that will make the message flash and possibly change the colour
of the text and background.
Thanks again
John

"Norman Jones" wrote:

Hi John,

Try something like:

'=============
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim arr As Variant
Dim i As Long

Set SH = Me.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("C1:C3") '<<==== CHANGE

arr = Array("Enter Reg. No", "Enter Colour Code", _
"Enter something else") '<<==== CHANGE

For Each rCell In rng.Cells
i = i + 1
If rCell.Value = arr(i - 1) Or rCell.Value = "" Then
MsgBox " All the cells in " & rng.Address(0, 0) _
& " on sheet " & SH.Name _
& " need to be completed!"
Cancel = True
Exit For
i = i + 1
End If
Next rCell

End Sub
'<<=============


---
Regards,
Norman


"John Davies" wrote in message
...
Thanks for the code, however I might have misled you, the fields that need
to
be completed, already have some data in them and these need to be
overtyped.
I presume that your code checks to see if the cells are blank, but can I
get
the message to pop up if some cells are already populated with speciifc
data.
eg. Cell C1 = "Enter Reg. No.", cell C2 = "Enter Colour Code" etc.

Thanks again for any help

"Norman Jones" wrote:

Hi John,

Try:

'=============
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range

Set SH = Me.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("A1:C1") '<<==== CHANGE

If rng.Cells.Count Application.CountA(rng) Then
Cancel = True
MsgBox " All the cells in " & rng.Address(0, 0) _
& " on sheet " '& SH.Name _
& " need to be completed!"
End If

End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"John Davies" wrote in message
...
I have a sheet that requires certain fields to be filled in before
printing.
Is it possible to have a message pop up if any of the fields have not
been
completed when I click on the print button.

Thanks in advance of any help.

John

message flash if sheet incomplete before printing
 

Hi John,

MsgBoxes do not have flashing or formatting capabilities.

In any event, I think that most people would find flashing screens abhorrent
and, thus, you risk alienating your users.

Additionally, having effectively alerted the user to the need to populate
the required cells, and having also aborted the print operation, why not
quit while you are ahead?


---
Regards,
Norman



"John Davies" wrote in message
...
Thanks Norman, that works fine, however to be a pain is it possible to
include code that will make the message flash and possibly change the
colour
of the text and background.
Thanks again
John

"Norman Jones" wrote:

Hi John,

Try something like:

'=============
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim arr As Variant
Dim i As Long

Set SH = Me.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("C1:C3") '<<==== CHANGE

arr = Array("Enter Reg. No", "Enter Colour Code", _
"Enter something else") '<<==== CHANGE

For Each rCell In rng.Cells
i = i + 1
If rCell.Value = arr(i - 1) Or rCell.Value = "" Then
MsgBox " All the cells in " & rng.Address(0, 0) _
& " on sheet " & SH.Name _
& " need to be completed!"
Cancel = True
Exit For
i = i + 1
End If
Next rCell

End Sub
'<<=============


---
Regards,
Norman


"John Davies" wrote in message
...
Thanks for the code, however I might have misled you, the fields that
need
to
be completed, already have some data in them and these need to be
overtyped.
I presume that your code checks to see if the cells are blank, but can
I
get
the message to pop up if some cells are already populated with speciifc
data.
eg. Cell C1 = "Enter Reg. No.", cell C2 = "Enter Colour Code" etc.

Thanks again for any help

"Norman Jones" wrote:

Hi John,

Try:

'=============
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range

Set SH = Me.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("A1:C1") '<<==== CHANGE

If rng.Cells.Count Application.CountA(rng) Then
Cancel = True
MsgBox " All the cells in " & rng.Address(0, 0) _
& " on sheet " '& SH.Name _
& " need to be completed!"
End If

End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is
maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"John Davies" wrote in message
...
I have a sheet that requires certain fields to be filled in before
printing.
Is it possible to have a message pop up if any of the fields have
not
been
completed when I click on the print button.

Thanks in advance of any help.

John