vendredi 13 octobre 2017

set caption for all checkboxes to reference cells in a range

I'm trying to find a way to set the captions for a group of checkboxes to a cell value, without having to manually program each checkbox. I have another macro that will generate checkboxes for any cell in P23:P33 that is not blank. The checkboxes line up beautifully 2 cells to the right. I'd like to set the caption for each of them to the cell value. So, if P23 says "Quick Connect", then I want the caption for the checkbox 2 cells over to say: "Quick Connect".

So far, I can only get it to set the same caption for all of them. I've tried nesting it within another For loop where i set a variable, such as: For i = 1 To 5, but I can only get it to make the caption whatever the last variable is... so they'll all have the caption "5".

Here's what I have so far. Let me know if you'd like me to post the code for generating the checkboxes. Essentially, I'd like to do it all at once. Generate the checkboxes, set the captions, define the name, etc.

Sub SetCaptions()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
    If TypeOf obj.Object Is MSForms.CheckBox Then
        obj.Object.Caption = "help me"
    End If
Next
End Sub




Aucun commentaire:

Enregistrer un commentaire