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]

so do you have a checkbox with the number? Also you didn't answer the question that Gary McMaster gave.

 

[klarowe]

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

I apologize... I'm new to VBA still so what determines somethign to be a shape vs object vs etc. I have no idea... just trying different things.. lol.

Basically I have a few different reports each an index page that has anywhere from 7 index items (each with an ActiveX checkbox) up to around 60 index items. Right now the very top code I posted is what I have... and it works fine. But if I need to add another sheet somewhere in the middle, I have to go through and change each checkbox code to match the new index. I know that I could always just add it at the end of the code and have the next checkbox, but I'm also trying to keep everything organized and in the correct order (both name and checkbox#) of how it appears on the index page.

Each checkbox next to the index item will hide/unhide that particular sheet depending on the value of the checkbox.

So what I am trying to do is come up with a "generic" code that will base off the checkbox # and hide a corresponding Sheet #. Then in each checkbox_click code I can simply call that macro and be done with it.

Sorry its so confusing... its hard to explain.

 

[texasalynn]

the question is how did you create the checkbox?

 

[klarowe]

so do you have a checkbox with the number? Also you didn't answer the question that Gary McMaster gave.

If I understand the question correctly, then yes. Each checkbox is named "checkbox#" (actual name, not written text nex to the box)

Not sure if it will help or not, but here is a screenshot of my index page. All checkboxes are in order from 1 at the top to 7 at the bottom (select / deselect all)

 

[klarowe]

the question is how did you create the checkbox?

I created it from the control toolbox toolbar (not the forms toolbar)

 

[klarowe]

Hopefully this will help explain it a little more... is there way to simplify this code so that I don't have to enter it for each and every sheet?
i.e. Sheet(i).visible = sheet2.checkbox(i-4).Value

Sub Chkbox()
    Sheet5.Visible = Sheet2.CheckBox1.Value
    Sheet6.Visible = Sheet2.CheckBox2.Value
    Sheet7.Visible = Sheet2.CheckBox3.Value
    Sheet8.Visible = Sheet2.CheckBox4.Value
End Sub

 

[texasalynn]

Ok so I'm understanding a little more of what you want, so I'll look at this. It might not be right away, so if anyone else has a solution, please go for it.

 

[klarowe]

I have gotten this far:

Sub Chkbox()
    For i = 3 To 7
        Sheets(i).Visible = ActiveSheet.OLEObjects("Checkbox" & i).Object.Value
    Next
End Sub

Which is actually working... but I need the checkbox "i" to be offset by -4 (checkbox1 is linked to sheet5). I've tried every way I can think of and can't get it to offset.

 

[klarowe]

I think I've got it... I had called out x= i - 4 but forgot to put "i-4" in parenthesis. Everything so far seems to be working now... but I'm not holding my breathe... lol

Sub Chkbox()
    For i = 5 To 10
        x = (i - 4)
            Sheets(i).Visible = ActiveSheet.OLEObjects("Checkbox" & x).Object.Value
    Next
End Sub

Now to go one further... lol. (and this is not a big deal at all... more of a curiousity). Is there a way to loop the actual check box procedures?
I.e.:
For i = 1 to 10
Sub Checkbox(i)_Click
'Run Macro'
End Sub
Next

 

[klarowe]

The more I think about it, nevermind trying to loop through the event... I think it would actually be better to not have it loop; that way if someone added a checkbox later down the road that was somehow out of order, the entire thing wouldn't end up in shambles... lol.

SO far, thank you both for your help... I'm sure I'll have something come up as I try applying this to all 21 reports so I shall not say good bye just yet... lol.