Use a variable in a reference to a control?

[3xDave]

I have been trying to figure this out off and on for many months now and there has to be a way.

Assume I have activex checkboxes named A1-A10.

This works:

Private Sub thisworks()
If Me.A1.Value = True Then
MsgBox "It's true"
End If
End Sub

This DOES NOT work:

Private Sub thisdoesnotwork()
myCtrl = "A1"
If Me(myCtrl).Value = True Then
MsgBox "It's true"
End If
End Sub

This is the only syntax I can find on the web after months of searching. I have tried all types of other ways but I am seriously missing something here. It would seem this type of variable referencing should be straightforward. No? LOL

The point is I want to use a loop such as this:

Private Sub usealoop()
For myCtrl = 1 to 10
 myCtrl = "A" & myCtrl
  If Me(myCtrl).Value = True Then
   MsgBox "It's true"
  End If
Next
End Sub

Help! Dave

 

[xld]

Private Sub usealoop()
For myCtrl = 1 to 10
  If Me.Controls("A" & myCtrl).Value Then
   MsgBox "It's true"
  End If
Next
End Sub

 

[sous2817]

Welcome to the board! Try:

Sub thisworks()
    Dim i As Integer
    For i = 1 To 10
    If Me.OLEObjects("A" & i).Object.Value = True Then
        MsgBox "It's True"
    End If
    Next i
End Sub

Modified from:
http://www.mrexcel.com/forum/showthread.php?t=375257&highlight=checkboxes+sheet


*edit: too slow*

 

[3xDave]

Thanks for the answer and the welcome. I've been reading the board for a while and it's been very helpful. I'll be sure to contribute as well.

The answer from sous2817 worked perfectly. The activex checkbox is obviously a member of the OLEObjects collection and I was missing the .Objects reference when I had tried that collection some time ago.


The answer from xld did not work for me as I get a "Method or data member not found" and it highlights the .Controls portion of this line.

Thanks!!