PDA

View Full Version : Procedurally generated checkbox code



QuinRiva
05-03-2007, 07:13 PM
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
05-03-2007, 10:15 PM
Instead of creating them on the fly and all the headaches that go with that why not just hide and unhide the checkboxes?
Sub a()
ActiveSheet.Shapes("Check Box 4").Visible = False
End Sub

QuinRiva
05-03-2007, 10:30 PM
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
05-03-2007, 10:32 PM
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.