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.

 

[texasalynn]

take a look at this

 

[Gary McMaster]

You might also pass the "CheckBox Object" itself to a common procedure. Something like the sample below.

Gary

Private Sub CheckBox1_Click()

'Pass the checkbox object to master procedure "CBRead"
CBRead CheckBox1

End Sub


Private Sub CheckBox2_Click()

'Pass the checkbox object to master procedure "CBRead"
CBRead CheckBox2

End Sub


Public Sub CBRead(oCheckBox As MSForms.CheckBox)
'Master procedure to handle common checkbox code

Debug.Print oCheckBox.Name

Select Case oCheckBox.Name

Case "CheckBox1"
    MsgBox "Code for checkbox 1 "
    
Case "CheckBox2"
    MsgBox "Code for checkbox 2"
    
'Case ... 'More check box names
    
Case Else
    MsgBox "Unknown checkbox"
    
End Select

End Sub

 

[klarowe]

Unfortunately I think that would be just as time consuming as my current setup. I was thinking something along these lines (and this code does not work and I'm probably way off, but it gives a little better idea what I'm trying to accomplish.

Sub Checkbox()
    Dim i As Integer
    For i = 1 To 55
        If i < 10 Then
            Sht = ("Sheet00" & i + 5)
        Else:
            Sht = ("Sheet00" & i + 5)
        End If
        Sheets("Index").Shapes("checkbox" & i).Value = Sht.Visible
    Next i
End Sub

Then under each checkbox I just call "Checkbox". Now when I add checkboxes, as long as I keep them in the same order as the sheets, I could just add the box with the call "checkbox" and be good

 

[klarowe]

Uh oh... have I actually stumped the pros on this one??? lol

 

[klarowe]

Let me clarify the "Sheet00" vs "Sheet0"; I have a macro that renames the code names of each sheet to Sheet001, Sheet002, etc. so that they stay in order in my VBA project screen. If changing them back to just sheet1, sheet2, etc will make things easier than I'm fine with that.

 

[klarowe]

With renaming the sheet code names back to the original, I have this code that is still giving me an error on the main line of code:

Sub Chkbox()
    Dim i As Integer
    x = i - 4
    For i = 5 To ThisWorkbook.Worksheets.Count
        Sheets(i).Visible = Sheets("Index").CheckBox(x).Value '<--- Object doesn't support this property error
    Next i
End Sub

 

[Gary McMaster]

What type of check boxes are you using? Your first code sample suggests they are ActiveX (containing a click event). Your second code sample references the "Shapes" collection which suggests that they may be Form check boxes.

Can you explain what you are trying to do without showing any code?

Gary

 

[texasalynn]

With renaming the sheet code names back to the original, I have this code that is still giving me an error on the main line of code:

Sub Chkbox()
    Dim i As Integer
    x = i - 4
    For i = 5 To ThisWorkbook.Worksheets.Count
        Sheets(i).Visible = Sheets("Index").CheckBox(x).Value '<--- Object doesn't support this property error
    Next i
End Sub

You are assigned x using the i variable before i has been defined. So you should move the x assignment or else change how you assign it.

 

[klarowe]

That makes sense. I fixe it, but it still gives me the same error.