Simplify this checkbox code

[klarowe]

Is there any way to simplify these checkbox codes. Preferably into a single code somehow so that if I add sheets and add to the index, I don't have to go through and re-write half of the code??? I know that I could just add to the end of the code each time I add a sheet, but I'm trying to keep it organized for the next person that may come along to try to figure it out (or even myself after a few years... lol).

Private Sub CheckBox1_Click()
    Sheets("1st Stg Impeller").Visible = CheckBox1.Value
    Application.Run "IndexNumber"
End Sub
 
Private Sub CheckBox2_Click()
    Sheets("2nd Stg Impeller").Visible = CheckBox2.Value
    Application.Run "IndexNumber"
End Sub
 
Private Sub CheckBox3_Click()
    Sheets("1st_2nd Stg Pinion").Visible = CheckBox3.Value
    Application.Run "IndexNumber"
End Sub
 
Private Sub CheckBox4_Click()
    Sheets("1st Stg Laby").Visible = CheckBox4.Value
    Application.Run "IndexNumber"
End Sub
 
Private Sub CheckBox5_Click()
    Sheets("2nd Stg Laby").Visible = CheckBox5.Value
    Application.Run "IndexNumber"
End Sub
 
Private Sub CheckBox6_Click()
    Sheets("1st Stg Tiebolt_Nut").Visible = CheckBox6.Value
    Application.Run "IndexNumber"
End Sub
 
Private Sub CheckBox7_Click()
    Dim i As Integer
    For i = 1 To 6
        ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = ActiveSheet.CheckBox7.Value
    Next i
End Sub

This example is just from my "test" report that I use for testing codes. A few of the real reports have upwards of 50-60 checkboxes... which becomes a major headache when modifying the code.

 

[klarowe]

See... Told ya.. lol. There is one thing that again isn't a big deal, more of a convenience thing if we can get it to work.

Is there a way to make it work with my current sheet names? (sheet001-sheet009, sheet010, sheet011, etc).

 

[klarowe]

****... missed the edit again.. Sheet001, etc are not actual "names" but rather the code names (Sheet1 changed to SHeet001 so that everything shows up in order on the VB project window) The actual names vary (ie Home, Index, etc)

 

[texasalynn]

ok so the sheet names are not "Sheet001" but rather something else???? Just for my own curiosity how did that happen? I've never seen them like that!

 

[klarowe]

The actual sheet names correspond with the index ("Home", "Index", "1st Stg Impeller", etc) Then I have a macro that renames the "code names" from "Sheetx" to "Sheet00x" so that it will keep the sheets listed in my project viewer in the same order that they appear on the report... which isn't a big deal, it just helps in negotiating through the pages.

For reference, here is the code I used:

Sub ChangeCodeNames()
    Dim i As Long
    For i = 1 To Worksheets.Count
        If i = 1 < 9 Then
            ActiveWorkbook.VBProject.VBComponents(Worksheets(i).CodeName).Properties("_CodeName") = "Sheet00" & i
        Else: ActiveWorkbook.VBProject.VBComponents(Worksheets(i).CodeName).Properties("_CodeName") = "Sheet0" & i
        End If
    Next i
End Sub

 

[Gary McMaster]

Based on your screen shot and the previous explanation and if I understand you correctly, I would be inclined to use "Form" check boxes in conjunction with "Application.Caller"

To try the following sample code:

1.) Create a brand new workbook with 3 worksheets named "Sheet1", "Sheet2" & "Sheet3" (the usual defaults)

2.) Add 3 "FORM" check boxes and name them the same as the worksheets.

3.) Assign the macro "Test" (below) to all 3 check boxes (Right click on each check box and select "Assign Macro" then pick "Test").

Public Sub Test()

Debug.Print Application.Caller 'For reference only

On Error Resume Next ' At least one worksheet must be visible or error.

'Application.Caller returns name of CheckBox which, by design, is same as sheet name.
If ActiveSheet.Shapes(Application.Caller).ControlFormat.Value = Checked Then
    ThisWorkbook.Worksheets(Application.Caller).Visible = True
Else
    ThisWorkbook.Worksheets(Application.Caller).Visible = False
End If

End Sub

If you need to add a new check box, put it wherever you want, assign the check box the same name as the worksheet it controls and assign the new check box the macro "Test" or whatever you decide to name the macro.

Hope this helps.

Gary

 

[klarowe]

How can I change the name of the form checkboxes while still leaving the text box blank?

 

[Gary McMaster]

Here's another version that'a a little more versatile. It will take the sheet name from an adjacent cell which is what I believe you have shown in your screen shot. You can change the "Caption" property to "" to suppress the text normally displayed in the check box (already done in this sample). There are a few lines of code included that depend on the "Caption" being the same as the sheet name and not "" as you have requested. I did not comment them out so you could try it if you wanted to. These lines are actually causing errors with the Caption = "" and should be removed.

This does not test for sheet names that do not exist. If you will be keying in the sheet names you will have to gaurantee they exist or add some error checking. I have only ignored the error in this sample (On Error Resume Next).

This sample will create check boxes on the fly for all existing sheets (except "Sheet1" which must exist unless you change the code).

To try it, paste the 4 procedures below into a standard module in a new workbook and then run "Checkbox_Create". The said procedure will delete all existing checkboxes each time you run it. Therefore you can add or remove sheets and run the said procedure multiple times.

Hope it helps.

Gary

Option Explicit
 
 
Public Sub Checkbox_Create()

Dim oCheckBox As CheckBox
Dim oTarget As Range
Dim oIndex As Worksheet
Dim oSheet As Worksheet

Dim lWidth As Long
Dim lHeight As Long

lWidth = 100
lHeight = 12

'Change sheet name (below) to desired worksheet
Set oIndex = ThisWorkbook.Worksheets("Sheet1")

'Change "A1" (below) to desired starting point
Set oTarget = oIndex.Range("A1")

'Clear column A
oIndex.Range("A:A").ClearContents

'Remove any existing check boxes
For Each oCheckBox In oIndex.CheckBoxes
    oCheckBox.Delete
Next oCheckBox

For Each oSheet In ThisWorkbook.Worksheets
    If oSheet.Name <> "Sheet1" Then
    
        oTarget.Value = oSheet.Name
        
        Set oCheckBox = oIndex.CheckBoxes.Add(oTarget.Offset(0, 1).Left, oTarget.Offset(0, 1).Top, lWidth, lHeight)
        With oCheckBox
            .Caption = ""
            '.Caption = oSheet.Name
            .OnAction = "Checkbox_Action"
            .Value = True
        End With
        
        Set oTarget = oTarget.Offset(1, 0)
    End If
Next oSheet

End Sub
 
 
Public Sub Checkbox_Action()

Debug.Print Application.Caller 'For reference only

Dim oIndex As Worksheet
Dim sText As String

On Error Resume Next

'Change sheet name (below) to match "oIndex" sheet name in "Checkbox_Create" procedure
Set oIndex = ThisWorkbook.Worksheets("Sheet1")

'Get the name of the worksheet from the Check Box "text frame"
If oIndex.Shapes(Application.Caller).ControlFormat.Value = Checked Then
    ThisWorkbook.Worksheets(oIndex.Shapes(Application.Caller).TextFrame.Characters.Text).Visible = True
Else
    ThisWorkbook.Worksheets(oIndex.Shapes(Application.Caller).TextFrame.Characters.Text).Visible = False
End If

'Or, if desired:

'Get the name of the worksheet from the adjacent cell in column A
sText = oIndex.Shapes(Application.Caller).TopLeftCell.Offset(0, -1).Value
If oIndex.Shapes(Application.Caller).ControlFormat.Value = Checked Then
    ThisWorkbook.Worksheets(sText).Visible = True
Else
    ThisWorkbook.Worksheets(sText).Visible = False
End If

End Sub
 
 
Public Sub CheckBoxes_Checked()

Dim oCheckBox As CheckBox
Dim oIndex As Worksheet

'Change sheet name (below) to desired worksheet
Set oIndex = ThisWorkbook.Worksheets("Sheet1")

For Each oCheckBox In oIndex.CheckBoxes
    oCheckBox.Value = True
    
    'Get the name of the worksheet from the Check Box caption ("text frame")
    ThisWorkbook.Worksheets(oCheckBox.Caption).Visible = True
    
    'Or, if desired
    
    'Get the name of the worksheet from the adjacent cell in column A
    ThisWorkbook.Worksheets(oCheckBox.TopLeftCell.Offset(0, -1).Text).Visible = True
    
Next oCheckBox

End Sub
 
 
Public Sub CheckBoxes_UnChecked()

Dim oCheckBox As CheckBox
Dim oIndex As Worksheet

'Change sheet name (below) to desired worksheet
Set oIndex = ThisWorkbook.Worksheets("Sheet1")

For Each oCheckBox In oIndex.CheckBoxes
    oCheckBox.Value = False
    
    'Get the name of the worksheet from the Check Box caption ("text frame")
    ThisWorkbook.Worksheets(oCheckBox.Caption).Visible = False
    
    'Or, if desired
    
    'Get the name of the worksheet from the adjacent cell in column A
    ThisWorkbook.Worksheets(oCheckBox.TopLeftCell.Offset(0, -1).Text).Visible = False
     
Next oCheckBox

End Sub

 

[klarowe]

I think I can work with that. Thanks for the help!