Procedurally generated checkbox code

[QuinRiva]

I currently want to use a check box on a contents page to show or hide worksheets in a workbook. Both the sheets and the checkboxes will be procedurally generated as part of a macro that updates a quarterly report.

If I use the check box from the forms toolbox (not the controls toolbox), I can assign a macro to the checkbox on creation so that when it is clicked it shows or hides the desired sheet. This is basically what I want it to do, note that both the worksheet and the check box that shows/hides it have the same name.

  Sub Showsheet()
              If ActiveSheet.Shapes(SHAPENAME) = True Then
                          Worksheets(SHAPENAME).show
              Elseif ActiveSheet.Shapes(SHAPENAME) = False
                          Worksheets(SHAPENAME).hide
              End If
  End

All checkboxes will run the same code (I can?t create new code for each checkbox because the checkboxes are procedurally generated), so how do I determine which check box called the macro?

I can?t think of any other way to do this. Also what?s the difference between the check boxes in the forms toolbox and the controls toolbox?

[lucas]

Instead of creating them on the fly and all the headaches that go with that why not just hide and unhide the checkboxes?
[VBA]Sub a()
ActiveSheet.Shapes("Check Box 4").Visible = False
End Sub[/VBA]

[QuinRiva]

It appears that I have found a solution:

  Sub Showsheet()
      Dim ChkName
      ChkName = Application.Caller
      If ActiveSheet.CheckBoxes(ChkName).Value = xlOn Then
          Worksheets(ChkName).Visible = True
      ElseIf ActiveSheet.CheckBoxes(ChkName).Value = xlOff Then
          Worksheets(ChkName).Visible = False
      End If
  End

Does anyone know of a more elegant one?

[QuinRiva]

Instead of creating them on the fly and all the headaches that go with that why not just hide and unhide the checkboxes?

I have no idea how many check boxes there are going to be. Additionally I won't be using the software it will be for my clients, so I want it pretty idiot proof.