How to set ActiveX combobox linkedCell property

Question

I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then

    If Not Intersect(Target, Range("A:A")) Is Nothing Then

        Dim cb As Object
        Dim combineRange As Range
        Dim boolStr As String
        Dim floatStr As String
        Dim booleanRange As Range
        Dim floatRange As Range

        Dim bRow As Integer
        bRow = Worksheets("DEF_BOOLEAN").Cells(Rows.Count, 1).End(xlUp).Row

        Dim fRow As Integer
        fRow = Worksheets("DEF_FLOAT").Cells(Rows.Count, 1).End(xlUp).Row

        boolStr = "A2:A" & bRow
        floatStr = "A2:A" & fRow

        Set booleanRange = Worksheets("DEF_BOOLEAN").Range(boolStr)
        Set floatRange = Worksheets("DEF_FLOAT").Range(floatStr)

        Set cb = Worksheets("FT_CASE_xx").OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height).Object


        For Each cell In booleanRange
            cb.AddItem cell.value
        Next cell
        For Each cell In floatRange
           cb.AddItem cell.value
        Next cell


    End If
End If
End Sub

which adds an ActiveX combobox each time I press on a Cell in the 'A' column. That's work fine. The problem is that I would like the cell below (the one 'covered' by the combobox) get the value selected in the combobox.

That's why I thought to use the linkedCell property. unfortunately the following lines don't work:

cb.LinkedCell = Target

or

cb.LinkedCell = Target.address

How should it be set to achieve the result?

BigBen BigBen · Accepted Answer · 2019-07-18T16:27:13

A slight change worked for me: change Dim cb As Object to Dim cb as ComboBox.

Then use cb.LinkedCell = Target.Address.

How to set ActiveX combobox linkedCell property

1 Answers