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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Thanks Lenze! That works beauuuuuuuuutifully. Much appreciated.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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:

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

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