Excel VBA - ComboBox selection to a cell?

Question

I programmatically add a new row and place a Combobox over a specific cell. The below code works, but I have two problems, described below the code sample. lastrow is a variable already defined. After inserting a new row, the below runs:

'add combobox list
Range("D" & lastrow, "D" & lastrow).Select
ActiveSheet.DropDowns.Add(ActiveCell.Left + 1, ActiveCell.Top + 1, 48, 18).Select
    With Selection
        .Name = "Combo & lastrow"
        .ListFillRange = "Ref!$C$6:$C$8"
        .LinkedCell = "D" & lastrow
        .DropDownLines = 3
        .Display3DShading = False
    End With

Issue 1: Apparently the combo box sits on top of the cell in the worksheet. When the user selects an item from the dropdown, the seleted item should be writen to the cell. The .linkedcell property seems like it would do this. However, the value written to the cell is a row number. For instance, in a 3-item list, where the user selects the 2nd item from the dropdown list, the cell's value is 2. Similarly, if the first item in the list is selected, the cell's value is 1. What is needed is the text in the cells from the ListFillRange.

Issue 2. If the user deletes a row, the ComboBox is not deleted, but instead just moves down to next row below. I have not found the VBA property for "Don't move or hide with cell", but experimenting with manually setting this from the dropdown format control does not seem to make a difference.

Thanks for your help.

Manually setting format control

Answer 1

Regarding Issue1: This is exactly how .LinkedCell is supposed to work. If you want to get the value of the selection in a cell, insert a formula like this: =INDEX(Ref!C6:C8,D5).

As a side note: I think you wanted to write .Name = "Combo" & lastrow instead of .Name = "Combo & lastrow".

Regarding Issue2: "Don't move or hide with cell" does not say "delete with cell". However, if you delete the row, then the LinkedCell property of the ComboBox will be #REF!, so you can write a function that iterates over Dropdowns, and deletes those that have an invalid LinkedCell:

Public Sub DeleteInvalidDropDowns()
    Dim dd As DropDown: For Each dd In ActiveSheet.DropDowns
        If dd.LinkedCell = "#REF!" Then
            dd.Delete
        End If
    Next
End Sub

The above function has to be called manually after each deletion. If you want it to be automatic, then add this event handler to the given worksheet (so, it should not be in Module1 but in e.g. Sheet1 in the VBA Editor):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dd As DropDown: For Each dd In Me.DropDowns
        If dd.LinkedCell = "#REF!" Then
            dd.Delete
        End If
    Next
End Sub