Reputation: 27
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
Upvotes: 0
Views: 395
Reputation: 29421
The LinkedCell
property is of String
type and stores/retrieves the address of the cell linked to the combobox
So you want to use
If objx.LinkedCell <>"" Then
.Range(objx.LinkedCell).Offset(0, 1).Select
'... rest of your code
End If
Upvotes: 1