Complex Print Button

[SaltySquid]

At my job, I travel to Navy Ships and test equipment. We have to print out large test booklets from an old Word document and any changes you make usually screws everything up. So I want to create an all inclusive test booklet creator in Excel. So my question is.

I have a series of checkboxes (we will say B3:B10) and next to each one is a hyperlink to a sheet within the workbook that houses the test sheet. Then next to that is a field where the tech will enter the amount of that test sheet they need. Then there will be a print button to print the entire test booklet.

How would I code the button to print like this. An example (since I work best off examples)
B3 is checked B4 is checked
C3 leads to Sheet 7 C4 leads to Sheet 9
D3 the tech entered 5 D4 = 3

All other checkboxes are "false".

So when the print button is pushed, 5 copies of Sheet 7 and 3 copies of Sheet 9 are printed.

The end "splash page" will have about 50-60 checkboxes. So it's going to be a big workbook (if that matters).

 

[rpaulson]

try this.

Sub Print_test()

    Dim ws As Worksheet

For r = 3 To 10 'set the the number of rows as needed

If Cells(r, "B") = True Then 'print this test if TRUE
    Set ws = Sheets(Cells(r, "C").Value)
    ws.PrintOut Copies:=Cells(r, "D")
End If

Next r

End Sub

-Ross

 

[kevin9999]

Another option:

Option Explicit
Sub CheckBox_Print()
    Dim cx As Excel.CheckBox
    Dim ws1 As Worksheet, ws As Worksheet, i As Long, s As String
    Set ws1 = Worksheets("Sheet1")      '<<< Change to actual sheet name with the checkboxes ***
    For Each cx In ws1.CheckBoxes
        On Error Resume Next
        If cx.Value = xlOn And ws1.Cells(cx.TopLeftCell.Row, 3) <> "" And ws1.Cells(cx.TopLeftCell.Row, 4) > 0 Then
            i = ws1.Cells(cx.TopLeftCell.Row, 4)
            With ws1.Cells(cx.TopLeftCell.Row, 3)
                s = Evaluate("textbefore(" & .Address(, , , 1) & ",{""!"",""'""})")
            End With
            Worksheets(s).PrintOut , , i
        End If
        On Error GoTo 0
    Next cx
End Sub