Combined Loops for Userform Controls & Sheet Ranges

  • Hi


    I'm trying to simplify the code below and have previously been able to but am finding my aging brain repeatedly unable to fathom the answer.

    I have a series of Checkboxes (numbered 73 to 99) that I need either a true or false value parsed to a respective cell within a range on a sheet.


    Long hand looks like this:

    Code
    'Loop Through Check Boxes 
                If CheckBox73.Value = True Then .Range("T8").Value = True Else .Range("T8").Value = False
                If CheckBox74.Value = True Then .Range("T9").Value = True Else .Range("T9").Value = False
                If CheckBox75.Value = True Then .Range("T10").Value = True Else .Range("T10").Value = False
                If CheckBox76.Value = True Then .Range("T11").Value = True Else .Range("T11").Value = False
                If CheckBox77.Value = True Then .Range("T12").Value = True Else .Range("T12").Value = False
                If CheckBox78.Value = True Then .Range("T13").Value = True Else .Range("T13").Value = False
                    '---- etc etc
                If CheckBox99.Value = True Then .Range("T34").Value = True Else .Range("T34").Value = False

    But I know there is a method to use For Loops to loop through both the Checkboxes and the cell range [ .Range("T8:T34") ]


    I tried various iterations of :


    Any pointers of how far off of it working would be gratefully received.


    Thanks all.

  • The best way to do this will be to create a Class Module called clsCheckBox. In the class module you'll add the Click event code that ill run whenever a CheckBox is clicked.


    Now you just need to attach the chkBox_Click event to each check box on your form.


    Next to get the CheckBoxes to work with the correct range enter each range in the appropriate CheckBox's Tag Property as T8, etc.


    See the example to see what I mean.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!