VBA for before print needed please

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Hello everyone, I am in need of some code. I would like to be able to enter a number into a cell, and have it change in increments that I specify in another cell. I would also like to specify the number of copies. For example I would enter 2 into a cell, and specify the number be increased by two for every sheet printed. Then I would input how many copies I want into another cell. Everytime each sheet is printed the number would change from 2 to 4 to 6 to 8 etc. This would be for a specific sheet in which I make running total tags for inventory purposes. Thank you very much.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
How about InPut Boxes?
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name <> "Tags" Then Exit Sub
Dim i, j, k As Long
On Error GoTo Trap
Application.EnableEvents = False
i = InputBox("Enter the Start Number")
j = InputBox("Enter the increment")
k = InputBox("How May Copies?")
Range("$A$1").Value = i
For x = 1 To k - 1
ActiveSheet.PrintOut
Range("$A$1").Value = Range("$A$1").Value + j
Next x
Trap: Application.EnableEvents = True
End Sub
lenze
 
Upvote 0
I can't test it right now, but I think this Adjustment is better
Rich (BB code):
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True 'Add this line
If ActiveSheet.Name <> "Tags" Then Exit Sub
Dim i, j, k As Long
On Error GoTo Trap
Application.EnableEvents = False
i = InputBox("Enter the Start Number")
j = InputBox("Enter the increment")
k = InputBox("How May Copies?")
Range("$A$1").Value = i
For x = 1 To k 'Remove the -1
ActiveSheet.PrintOut
Range("$A$1").Value = Range("$A$1").Value + j
Next x
Trap: Application.EnableEvents = True
End Sub

lenze
 
Upvote 0
Lenze, I have one problem with the code. It won't let me print any of the other sheets in the workbook. Is there a way to solve this problem? Other than that it works fine.

I am using the second code you posted.

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True 'Add this line
If ActiveSheet.Name <> "Running_Total_Tags" Then Exit Sub
Dim i, j, k As Long
On Error GoTo Trap
Application.EnableEvents = False
i = InputBox("Enter the Start Number")
j = InputBox("Enter the increment")
k = InputBox("How May Copies?")
Range("$F$23").Value = i
For x = 1 To k 'Remove the -1
ActiveSheet.PrintOut
Range("$F$23").Value = Range("$F$23").Value + j
Next x
Trap: Application.EnableEvents = True
End Sub
 
Upvote 0
I went all self reliant on the problem, and fixed it. I just had to move the Cancel = True down one line.
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name <> "Running_Total_Tags" Then Exit Sub
Cancel = True 'Add this line
Dim i, j, k As Long
On Error GoTo Trap
Application.EnableEvents = False
i = InputBox("Enter the Start Number")
j = InputBox("Enter the increment")
k = InputBox("How May Copies?")
Range("$F$23").Value = i
For x = 1 To k 'Remove the -1
ActiveSheet.PrintOut
Range("$F$23").Value = Range("$F$23").Value + j
Next x
Trap: Application.EnableEvents = True
End Sub
 
Upvote 0
Could I ask a stupid question - what error are you trying to trap?

Also, do you realise that the code could possibly be calling itself repeatedly.:)

PS Just noticed that the variables aren't declared 'properly' - probably not a problem though.
Code:
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Long
 
    Set ws = ActiveSheet
    
    With ws
 
        If .Name <> "Running_Total_Tags" Then Exit Sub
 
        Cancel = True
 
        Application.EnableEvents = False
 
        i = InputBox("Enter the Start Number")

        j = InputBox("Enter the increment")

        k = InputBox("How May Copies?")
 
        .Range("$F$23").Value = i
 
        For x = 1 To k    'Remove the -1
            .PrintOut
            .Range("$F$23").Value = .Range("$F$23").Value + j
        Next x
 
    End With
    
    Application.EnableEvents = True
 
End Sub
 
Last edited:
Upvote 0
Is there any way to switch from my default printer to a network printer before this prints, and then switch back, or to have the print dialogue screen popup so that I can choose the printer myself?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top