Change Print Command or add a new print command

I have a a macro button on my excel sheet that will clean up the gray areas
on my sheet. I would like to know if it is possible to do this if someone
clicks on the print icon instead of having this function?

Any ideas?

Thank you,

Daniel Young

Hi Daniel,

Try:
'==============>>
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Cancel = True

'Your Code

Application.EnableEvents = True

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



"Daniel R. Young" <DanielRYoung@discussions.microsoft.com> wrote in message
news:97CCF043-B7D9-42F8-8CFF-E25B6D50E61D@microsoft.com...
>I have a a macro button on my excel sheet that will clean up the gray areas
> on my sheet. I would like to know if it is possible to do this if someone
> clicks on the print icon instead of having this function?
>
> Any ideas?
>
> Thank you,
>
> Daniel Young

When I hit the print button it still printed without my area cleaning. Here
is the code I used:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Cancel = True

Worksheets("Report").Unprotect
Dim rng As Range, c As Range
Set rng = Selection
For Each c In rng
If c.Interior.ColorIndex = 15 Then
c.Interior.ColorIndex = 2
End If
Next c
For Each c In rng
If c.Font.ColorIndex = 5 Then
c.Font.ColorIndex = 2
End If
Next c

Worksheets("Report").Protect
ws_exit:

Application.EnableEvents = True
End Sub


"Norman Jones" wrote:

> Hi Daniel,
>
> Try:
> '==============>>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Application.EnableEvents = False
> Cancel = True
>
> 'Your Code
>
> Application.EnableEvents = True
>
> 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
>
>
>
> "Daniel R. Young" <DanielRYoung@discussions.microsoft.com> wrote in message
> news:97CCF043-B7D9-42F8-8CFF-E25B6D50E61D@microsoft.com...
> >I have a a macro button on my excel sheet that will clean up the gray areas
> > on my sheet. I would like to know if it is possible to do this if someone
> > clicks on the print icon instead of having this function?
> >
> > Any ideas?
> >
> > Thank you,
> >
> > Daniel Young
>
>
>

Hi Daniel,

> When I hit the print button it still printed without my area cleaning.
> Here
> is the code I used:

As your code does not issue any print command, I suspect that you have
inadvertently turned off application events and, consequently, your code is
not running.

To ensure that the Application.Events setting is restored, run the
following:

'============>>
Sub AAA
Application.EnableEvents = True
End sub
'<<============

I have added a print instruction to your code and reduced the two loops to a
single loop. I also added an instruction to limit the code to the Reort
sheet, so that other sheets can print normally.

Try:

'==============>>
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range, c As Range

If ActiveSheet.Name <> "Report" Then Exit Sub

On Error GoTo ws_exit

Application.EnableEvents = False
Cancel = True

Worksheets("Report").Unprotect

Set rng = Selection

For Each c In rng
If c.Interior.ColorIndex = 15 Then
c.Interior.ColorIndex = 2
ElseIf c.Font.ColorIndex = 5 Then
c.Font.ColorIndex = 2
End If
Next c

Worksheets("Report").Protect

ActiveSheet.PrintOut

ws_exit:
Application.EnableEvents = True

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


---
Regards,
Norman


"Daniel R. Young" <DanielRYoung@discussions.microsoft.com> wrote in message
news:8FC61F57-4485-47F2-8718-2FD04BE6B6A9@microsoft.com...
> When I hit the print button it still printed without my area cleaning.
> Here
> is the code I used:
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Application.EnableEvents = False
> Cancel = True
>
> Worksheets("Report").Unprotect
> Dim rng As Range, c As Range
> Set rng = Selection
> For Each c In rng
> If c.Interior.ColorIndex = 15 Then
> c.Interior.ColorIndex = 2
> End If
> Next c
> For Each c In rng
> If c.Font.ColorIndex = 5 Then
> c.Font.ColorIndex = 2
> End If
> Next c
>
> Worksheets("Report").Protect
> ws_exit:
>
> Application.EnableEvents = True
> End Sub
>