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

Change Print Command or add a new print command
 

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" wrote in message
...
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