Need Help for 1 click print with data calculation include

alienalvan · May 14th 2013

Currently i'm using the script below to do a click print + value change.

Is there any way to make it like 1 click print with multiple printings with different value?

1 click print out 10 copies each with different +1 value?

Thanks in advance.

Code

Private Sub CommandButton1_Click()




Sheets("Sample").PrintOut Copies:=1, Collate:=True




Dim x As Integer
Dim y As Integer




x = Range("D7").Value




x = x + 1
Range("D7").Value = x




y = Range("F7").Value




y = y + 1
Range("F7").Value = y




End Sub

Display More

Brian Walters · May 14th 2013

Re: Need Help for 1 click print with data calculation include

Try using a loop. For example:

Code

Private Sub CommandButton1_Click()     
     
    Dim x As Integer 
    Dim y As Integer 
    Dim i As Integer 
    
    For i = 0 to 9 
      Range("D7").Value = Range("D7").Value + i 
      Range("F7").Value = Range("F7").Value + i
      Sheets("Sample").PrintOut Copies:=1, Collate:=True
    Next i 
     
End Sub

Display More

maudibe · May 15th 2013

Re: Need Help for 1 click print with data calculation include

Brian,

Your code will get the job done but there is a typo that will increment the cells by i not by one. I know that this is what your mind was saying but your fingers were off on an tagent

Code

Private Sub CommandButton1_Click()
     
    Dim x As Integer
    Dim y As Integer
    Dim i As Integer
     
    For i = 0 To 9
        Range("D7").Value = Range("D7").Value + 1
        Range("F7").Value = Range("F7").Value + 1
        Sheets("Sample").PrintOut Copies:=1, Collate:=True
    Next i
     
End Sub

Display More

Smallman · May 15th 2013

Re: Need Help for 1 click print with data calculation include

Err...... (i ) will always be 1 in Brian's example. So Brian (welcome back to the forum by the way) your code is perfecto!!! Well done.

Slight variation which will produce the same result.

Code

Private Sub CommandButton1_Click()
Dim i As Integer
    For i = 1 To 10
        Range("D7, F7").Value = i
        Sheets(1).PrintOut 1, True
    Next i
End Sub

Take care

Smallman

maudibe · May 15th 2013

Re: Need Help for 1 click print with data calculation include

Quote

1 click print out 10 copies each with different +1 value?

Err.....using (i) in Bran's example, since (i) increments, a 5 in D7 and a 6 in F7 will produce a 50 and 51 as opposed to calculating a 15 and 16 respectively.

Quote

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
Range("D7, F7").Value = i
Sheets(1).PrintOut 1, True
Next i
End Sub

Display More

Err......Whereas, any values placed in D7 and F7 wil yield a 10 in both cells using small man's routine since it is simply assigning the values of the cells to what ever (i) has incremented to. This does not take into consideration the starting values of the cells.

alienalvan · May 16th 2013

Re: Need Help for 1 click print with data calculation include

Wow, thanks for all the advise guys! It's very helpful, at the end i come out with this format :tongue:

I wonder is there anyway for emergency script stop, so that if i find the printing is wrong i can stop immediately.

Code

Private Sub CommandButton1_Click()




With ActiveSheet.PageSetup
    .LeftMargin = Application.InchesToPoints(0.1)
    .RightMargin = Application.InchesToPoints(0.1)
    .TopMargin = Application.InchesToPoints(0.1)
    .BottomMargin = Application.InchesToPoints(1.2)
    .HeaderMargin = Application.InchesToPoints(0.1)
    .FooterMargin = Application.InchesToPoints(0.2)
    .CenterHorizontally = True
    .CenterVertically = False
    .Orientation = xlPortrait
    .BlackAndWhite = True
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintErrors = xlPrintErrorsDisplayed
End With




Dim x As Integer
Dim y As Integer
Dim i As Integer
x = Range("D7").Value
y = Range("F7").Value




For i = 1 To TextBox1.Value
Sheets("Sample").PrintOut Copies:=1, Collate:=True
Range("D7").Value = Range("D7").Value + 1
Range("F7").Value = Range("F7").Value + 1
Next i




End Sub

Display More

Brian Walters · May 16th 2013

Re: Need Help for 1 click print with data calculation include

CTRL + Pause\Break will stop the macro in debugging mode.

alienalvan · May 16th 2013

Re: Need Help for 1 click print with data calculation include

Quote from Brian Walters;664760

CTRL + Pause\Break will stop the macro in debugging mode.

OIC, then i'll need to make a easy hotkey for that. Thanks man

Need Help for 1 click print with data calculation include

Participate now!

Share