Referencing a series of controls in Excel VBA

[afryer]

Hi All,

I am producing a spreadsheet which contains a series of controls in column B,named CheckBox1....CheckBoxn (not very original I know, but there is about 600 of them and I can't be bothered to change them by hand).

What I need to be able to do is to reference the controls based on the column number that is selected, for example if the user clicked on row 24 then I would need to reference CheckBox24.

I can reference the individual objects by using me.CheckBox1, but I need to have an object that I can use, i.e

l_value = lCheckBoxObject.Value

will return the current state of the check box, which can be any of the check boxes from 1..n.

Does anybody have any idea how to do this?

Thanks

 

[PHV]

You may have to play with the Shapes collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[taupirho]

Try this out. The exact solution may depend on whether you are using userform checboxes or control checkboxes

For Each ctrl In ActiveSheet.Shapes

If Left(ctrl.Name, 8) = "CheckBox" And
Mid(ctrl.Name, 9) = ActiveCell.Row Then

ctrl.DrawingObject.Object.Value = True

End If
Next