Is there a way to run a macro when excel's print function is selected?

tazmtiger

Board Regular
Joined
Jul 7, 2005
Messages
194
Hi, Is there a way to recall a VBA macro when print button or print function is selected???

Currently I have a select a print range though a command button, but. I want to incorporate that function to the excel print button or print option.

Is that possible??

Please advice!

Thank you!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try in the ThisWorkbook module

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'
'your code goes here
'
End Sub
 

tazmtiger

Board Regular
Joined
Jul 7, 2005
Messages
194
Thank you Vog! It worked great!

I have another dilemma with my code, I get a Run-time error "424" object required. when nothing is selected, or when I press cancel.

My code below:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim RngToPrint As String

RngToPrint = Application.InputBox("Note: This print function allows you to enter, or select a print range, or print area. Start, by selecting the first cell and last cell you want to print. Example: A1:R29 This will start printing from cell A1 and end print on cell R29. ", "Enter your Print Area -- Example: (A1:R29)", Type:=8).Address

For Each sh In ThisWorkbook.Sheets
sh.PageSetup.PrintArea = RngToPrint
Next
End Sub




Is there a way to fix that?

Thank you!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Rng As Range, sh As Worksheet
On Error Resume Next
Set Rng = Application.InputBox("Note: This print function allows you to enter, or select a print range, or print area. Start, by selecting the first cell and last cell you want to print. Example: A1:R29 This will start printing from cell A1 and end print on cell R29. ", "Enter your Print Area -- Example: (A1:R29)", Type:=8)
If Rng Is Nothing Then Exit Sub
On Error GoTo 0
For Each sh In ThisWorkbook.Sheets
    sh.PageSetup.PrintArea = Rng.Address
Next
End Sub
 

tazmtiger

Board Regular
Joined
Jul 7, 2005
Messages
194
Awesome work Vog! Thank you so much for your time and help! Always appreciated!
 

Burrswood

New Member
Joined
Apr 10, 2013
Messages
1
Try in the ThisWorkbook module

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'
'your code goes here
'
End Sub

Hello, I am trying to use the Workbook before print but cannot get it to work. It is all set up properly in the workbook module. I am trying to call 2 procedures before printing. The procedures work fine when called manually or from elsewhere, but not from the "Private Sub Workbook_BeforePrint(Cancel As Boolean)" I cleared out all code and tested with just a simple message box and that didn't execute either. Any ideas????
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top