Using a for loop to access multiple objects RRS feed

  • Question

  • I've got a series of check boxes and text fields in a User Form, and I want the text fields to write to an Excel sheet only if the corresponding check boxes are checked.

    The boxes are named "OvrRideChk1", "OvrRideChk2", "OvrRideChk3", ...

    The text fields are named "OvrRideValue1", "OvrRideValue 2", "OvrRideValue 3", ...

    There are 100 of each within my User Form.

    When I click the OK button to run the code below, I get an error: "Method or data member not found".

    Private Sub OK_Click()
        Dim i As Integer
        With Worksheets("Overrides").Range("C2")
            For i = 1 To 100
                'Clear all override cells
                .Offset(i - 1, 0).Value = ""
                'Write the chosen overrides to the sheet
                If Me.OLEObjects("OvrRideChk" & CStr(i)).Value = True Then
                    .Offset(i - 1, 0).Value = Me.OLEObjects("OvrRideValue" & CStr(i)).Value
                End If
            Next i
        End With
        'Quit the form
        Unload Me
    End Sub

    What am I doing wrong?


    Thursday, October 3, 2013 9:12 PM