message flash if sheet incomplete before printing

[John Davies]

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

[Norman Jones]

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" <JohnDavies@discussions.microsoft.com> wrote in message
news:016A6606-C0AB-4F0F-B046-5800E4F70A9C@microsoft.com...
>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

[John Davies]

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" <JohnDavies@discussions.microsoft.com> wrote in message
> news:016A6606-C0AB-4F0F-B046-5800E4F70A9C@microsoft.com...
> >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
>
>
>

[Norman Jones]

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" <JohnDavies@discussions.microsoft.com> wrote in message
news:C740462D-43E5-4C36-91A5-CE83EA056DCC@microsoft.com...
> 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" <JohnDavies@discussions.microsoft.com> wrote in message
>> news:016A6606-C0AB-4F0F-B046-5800E4F70A9C@microsoft.com...
>> >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
>>
>>
>>

[John Davies]

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" <JohnDavies@discussions.microsoft.com> wrote in message
> news:C740462D-43E5-4C36-91A5-CE83EA056DCC@microsoft.com...
> > 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" <JohnDavies@discussions.microsoft.com> wrote in message
> >> news:016A6606-C0AB-4F0F-B046-5800E4F70A9C@microsoft.com...
> >> >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
> >>
> >>
> >>
>
>
>

[Norman Jones]

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" <JohnDavies@discussions.microsoft.com> wrote in message
news:C26EEF72-2AF5-4508-ABE9-D329407006CC@microsoft.com...
> 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" <JohnDavies@discussions.microsoft.com> wrote in message
>> news:C740462D-43E5-4C36-91A5-CE83EA056DCC@microsoft.com...
>> > 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" <JohnDavies@discussions.microsoft.com> wrote in message
>> >> news:016A6606-C0AB-4F0F-B046-5800E4F70A9C@microsoft.com...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>