Clear and Print tabs in a worksheet with checkboxes

[aejazzy]

I have a workbook with 100 tabs and I have an index page with checkboxes for each tab. I need a macro that will clear all of the checkboxes on the page with one button and a separate macro that will print only the checked tabs.

Can anyone write that code for me so I can just add it into my index tab? I can email you the workbook if needed. Thanks for your quick reply.

-Amy-

 

[Datsmart]

You didn't say if these are Forms checkboxs or Controls checkboxs.
This will uncheck all of either type of CheckBoxs on the active worksheet:

Sub uncheck_all()
Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        If sh.Type = msoOLEControlObject Then
            If TypeName(sh.OLEFormat.Object.Object) = "CheckBox" Then sh.OLEFormat.Object.Object = False
        End If
        If sh.Type = msoFormControl Then
            If sh.FormControlType = xlCheckBox Then sh.OLEFormat.Object = False
        End If
    Next sh
End Sub

As for Printing just the checked Worksheets, I would need more information.
1. What type of checkboxes, Forms or Control?
2. How can I identify which checkbox goes with what worksheet?

 

[aejazzy]

John, thanks for your help. I added the uncheck macro and it worked perfectly. I added a command button from the control toolbox and made it so the macro changed on the button click. I wonder if you also know how to change the text on the command button to Uncheck boxes instead of command button?

Also, to your second question. I added the checkboxes from the control toolbox and they are numbered from 1 - 228. I hope this helps. If you answer these two questions I will have my worksheet done. Thanks so much.

-Amy-

 

[aejazzy]

By the way, the boxes right now are not linked to the hyperlink for the tab. This should answer your second question John.

-Amy-

 

[Datsmart]

Sorry for the delay in replying, other obligations.

This code will print out the worksheets that have a checkbox checked.

Sub PrintCheckedTabs()
    For Each sh In ActiveSheet.Shapes
        If sh.Type = msoOLEControlObject Then
            If TypeName(sh.OLEFormat.Object.Object) = "CheckBox" Then
                If sh.OLEFormat.Object.Object = True Then
                    myRow = sh.OLEFormat.Object.TopLeftCell.Row
                    Worksheets(Cells(myRow, "B").Value).PrintOut
                    MsgBox "WorkSheet " & Cells(myRow, "B").Value & " is Printing."
                End If
            End If
        End If
    Next sh
End Sub

This code assumes the worksheet with your Controls checkboxes is active.
Also, you have checkboxs in column A and the name of each worksheet in column B. The code obtains the row number of each checked checkbox. Then the corresponding worksheet name in column B of that row will be printed out. For test purposes, change ".PrintOut" to ".PrintPreview".
There is no error checking, the code assumes each of your named worksheets exist.
I included a Message Box to slow the code down between Print Jobs.

 

[Datsmart]

In a private message you asked about assigning macros to buttons. Here is some general information on that subject:
Two button types are available plus you can use a Drawing Toolbar shape as a button.

1. Forms Button
Excel ToolBar: View - ToolBars - Forms, then drag the button to your sheet. You can re-position later. It will ask you for the macro to run, so it easier if the macro is built first.
Right click "Format Control" to edit font color and text.
You can not change the background color of a Forms button, just the text color.

2. Controls Button
Excel ToolBar: View - ToolBars - Controls, drag...
This button when Right-Clicked (in Design Mode) has the "view code" option which opens the click event for that button. These buttons have much more flexability and the button colors can be changed, unlike the Forms button.
Color and other options are setup in the Properties Dialog box. At first it is a little confusing but the changes can be worthwhile.
To change the Text of a Control button, right click, under CommandButton Object, choose Edit.
Must use "Design Mode" to set this button up, then turn off design mode to use the button.

3. Shapes Button, (My favorite)
You might condider using a Shapes Button.
You can build this button from the Drawing Toolbar.
Right click the ToolBar area: Click Drawing to open the Drawing Toolbar.
Click AutoShapes
Click "Basic Shapes" | Click Bevel, then drag to size on your worksheet
You can easily customize this 3-D button by right clicking it and choosing Format AutoShape…
You can add color, shadows, change font, adjust properties, etc.
You assign macros to this button just like you do with a Forms button, right click and choose "Assign Macro".
No need to use design mode. This button is much easier to setup than a Control Button.

Here is a link to some Macro Basics that you might find helpful:
http://www.datapigtechnologies.com/flashfiles/spreadsheetcontrols.html

 

[aejazzy]

John,

Thanks so much, I was able to add it quickly this way. Still a little confused with the print macro but I will play with it and see what I can do.

Thanks again!

-Amy-