Printing with Checkbox userform

[jgalas]

Hello!
I'm a nuby in vba code.... i have searched in forum but i can find what i need...

Let's say i have a userform with 20 checkbox, when one or several are true i want to print a area on sheet1.
Data on sheet1 change with the value of range ("AF2") in same sheet.
Range ("AF2") value came from checkbox (1,2,3...,20)

I have manage this code, it only print sheet with the last value of range ("AF2"):

Private Sub CommandButton1_Click()
Dim MyRange As Range
Set MyRange = Range("A1:z49")

With ActiveSheet.PageSetup
.TopMargin = 0.12 * 72
.LeftMargin = 0.25 * 72
.BottomMargin = 0.14 * 72
.RightMargin = 0.21 * 72
.HeaderMargin = 0.07 * 72
.FooterMargin = 0.05 * 72
.PrintArea = MyRange.Address
End With


If CheckBox1.Value = True Then Range("AF2") = 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1

If CheckBox2.Value = True Then Range("AF2") = 2
ActiveWindow.SelectedSheets.PrintOut Copies:=1
.....

End Sub

Thanks a lot

 

[jgalas]

Anyone!!!
Msg doesn't clear?...

 

[Andrew Poulsom]

The way your code is written the sheet will be printed twice irrespective of the state of the CheckBoxes, because your print statement is outside the If ... Then construct. It should be like this:

If CheckBox1.Value = True Then
    Range("AF2") = 1
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If

If the data in A1:Z49 isn't changing when AF2 changes, maybe Calculation is set to manual.

 

[jgalas]

The way your code is written the sheet will be printed twice irrespective of the state of the CheckBoxes, because your print statement is outside the If ... Then construct. It should be like this:

If CheckBox1.Value = True Then
    Range("AF2") = 1
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If

If the data in A1:Z49 isn't changing when AF2 changes, maybe Calculation is set to manual.

Hi Andrew,
Thanks for you anwser, the Calculation it set Auto.
And the data in A1:z49 is changing depending of data in AF2.
What i say is if i choise the checkbox value 1 and 15 it print only the data of the 15 choise several times.


Can i have this code for the 20 choise i can make:
If CheckBox1.Value = True Then
Range("AF2") = 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If CheckBox1.Value = True Then
Range("AF2") = 2
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
....
If CheckBox1.Value = True Then
Range("AF2") = 20
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
End sub

 

[Andrew Poulsom]

Yes, but make sure that you are referring to the correct CheckBoxes. In the code you posted you have CheckBox1 three times.

 

[jgalas]

Yes, but make sure that you are referring to the correct CheckBoxes. In the code you posted you have CheckBox1 three times.

True.... copy/paste. Sorry

I will test the code

Thanks

 

[jgalas]

it dont work..
I have chaged the printing comand to a preview... like this


If CheckBox1.Value = True Then Range("AF2") = 1 ActiveWindow.SelectedSheets.PrintPreview End If</pre>And put the 20 IF/End, changing checkbox # and AF2 value...

It preview the first true checkbox and crach...

Mayby i start it over with another solution...
I have you a solution?

 

[Andrew Poulsom]

It crashes with what error? I don't see anything in your code that shouldn't work.

 

[jgalas]

It crashes with what error? I don't see anything in your code that shouldn't work.

Excel freeze... i must shut it down ??

I'm thinking to use checkbox controlo in the sheets with cell control format and when the cell its true it rum a code to print the selected area.

A column A with 20 row - 1 to 20, in column B - Tue or false depending of checkbox

Code, which i dont know how to made yet, what check column B and if it true put the corespondent value of column A in $AF$2 and print...

I'm trying to make it works