CODEWITHSUNDEEP
excelvbacheckboxactivex
Drag00n
Drag00n

Reputation: 27

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

Upvotes: 0

Views: 395

Answers (1)

user3598756
user3598756

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

Related Questions