Solved: Macro for printing

[hunsnowboard]

Hi Everyone!

I have the following problem and I just can’t solve it.

I have an excel file with two worksheets. One worksheet contain the data of all the countries with all the detailed information. The other worksheet is the print version for my boss. It is a formatted table with just a few data. On this sheet I am able to choose the countries by a drop down list, and when I choose the relevant country, I can see below the figures. My problem is that when I want to print out the information on one country, it works perfectly, I just select the relevant country and then print it. But in case I would like to print all the countries, or just 20 countries I need to select them one by one and print them one by one.

How could I make a print button which when pressed brings me up a userform when I can select the countries with the help of checkboxes and then have the checked ones printed, like it is ont he formatted spreadsheet.

I hope you can understand my problem and you can help me!

Thank you a lot in advance and have a nice weekend!

[mdmackillop]

Give this a try

[Tinbendr]

Here's an alternative selection process. (See sheet2)

[Paul_Hossler]

Mac -- Yours seems to assume that the checkboxes are named "CheckBox1", "CheckBox2", etc.

While that might be the case, I usually rename them, or at least add and delete so often that my numbering is not consistant.

Maybe something like this avoids that assumption


[VBA]
Private Sub UserForm_Initialize()
Dim Rng As Range
Dim oCntrl As Control
Dim iCellCnt As Long

Set Rng = Range("Countries")

iCellCnt = 0
For Each oCntrl In Me.Controls
If TypeName(oCntrl) = "CheckBox" Then
iCellCnt = iCellCnt + 1
oCntrl.Caption = Rng.Cells(iCellCnt, 1).Text
End If
Next
End Sub
Private Sub CommandButton1_Click()
Dim oCntrl As Control
For Each oCntrl In Me.Controls
If TypeName(oCntrl) = "CheckBox" Then
If oCntrl.Value = True Then
Cells(1, 1) = oCntrl.Caption
Calculate
ActiveSheet.PrintOut
End If
End If
Next
Unload UserForm2
End Sub
[/VBA]

Paul

[hunsnowboard]

Thank you very much for the solution! It works great!

One last question: how can I add two buttons to the userform where one button will select all the checkboxes, the other will deselect all checkboxes?

Thank you very much in advance!

[mdmackillop]

[VBA]Private Sub CommandButton2_Click()
For Each ctrl In Me.Controls
If Left(ctrl.Name, 5) = "Check" Then
ctrl.Value = True
End If
Next
End Sub
[/VBA]

[hunsnowboard]

Thank you very much! Everything works great! Thank you!