samedi 21 mai 2016

activex checkbox loop- selects linked cell offset then copies row

I have about 50 checkboxes that are all linked to the cell that they are positioned on(eg checkbox in A2 is linked to cell A2). I have the loop working to a degree. The issue i am having is making the linked cell selected and the offset by 1 cell then copy the row leaving out the linked cell. then pastes to different worksheet to the next blank row.

Sub CheckboxLoop() Dim objx As OLEObject Dim lastrow As Range

Application.ScreenUpdating = False

'Loop through Checkboxes With ActiveSheet For Each objx In .OLEObjects If TypeName(objx.Object) = "CheckBox" Then If objx.Object.Value = True Then If objx.Object.LinkedCell = True Then 'runtime error 438 object doesn't support this property or method objx.Object.LinkedCell.Offset(0, 1).Select Range(Cells(Selection.Row, 1), Cells(Selection.Row, 3)).Select Selection.Copy Worksheet("Data").Select Worksheet("Data").Range("A1").End(xlDown).Offset(1, 0).Select Selection.PasteSpecial (xlPasteValues) Application.CutCopyMode = False

            End If

        ElseIf objx.Object.Value = False Then


        ElseIf IsNull(objx.Object.Value) Then


        End If
    End If
Next

End With Application.ScreenUpdating = True End Sub

When i step through the macro is all fine until i get to the 3rd if and i receive the runtime error 438 object doesn't support this property or method

Any help would be awesome thanks




Aucun commentaire:

Enregistrer un commentaire