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