activex checkbox loop- selects linked cell offset then copies row

Question

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

user3598756 · Accepted Answer · 2016-05-21 10:08:39Z

1

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

edited May 21, 2016 at 10:08

answered May 21, 2016 at 8:56

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Drag00n Over a year ago

thanks, but it still has the runtime error with this line If objx.Object.LinkedCell <>"" Then

Drag00n Over a year ago

Thanks that worked ... once lol. but anyway. now its Run-Time error 1004 with Worksheets("Data").Range("A1").End(xlDown).Offset(1, 0).Select

user3598756 Over a year ago

If I fullfilled your original question please mark my answer as accepted. Thank you. As for your new issue start a new question

Collectives™ on Stack Overflow

activex checkbox loop- selects linked cell offset then copies row

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related