Programmatically Set Excel LinkedCell Property with VBA

Yesterday I was working with an Excel document that contained Combobox form controls.

I wanted to count the number of cells containing a particular value using the COUNTIF formula. However the count returned 0 because the LinkedCell property of the Combobox was not set to the Cell that contained the Combobox.

To set the LinkedCell Ctrl-Click the Combobox to select it, right-click and select Format Control. Then set the correct Cell in the Cell link field:

SNAGHTML18f5a4ba

My sheet contained about 150 Comboboxes, so obviously I was going to do this using a script. I couldn’t find anything useful with Google so I wrote my own Macro.

The Macro assumes that the sheet containing the Comboboxes is the active sheet and only Comboboxes are touched. It also assumes that Comboboxes are places within Cells an do not overlap multiple Cells.

Sub SetLinkedCells()
    Dim Shp As Shape
    Dim Count As Integer
    Count = ActiveSheet.Shapes.Count
    Dim i As Integer

    For i = 1 To Count
        Set Shp = ActiveSheet.Shapes(i)

        ' Update Progress
        Application.StatusBar = "Progress: " & i & " of " & Count & ": " & Format(i / Count, "0%")

        ' Only process Comboboxes
        If Shp.FormControlType = xlDropDown Then
            ' 64 is added to Column to map column 1 to A, column 2 to B etc
            Shp.DrawingObject.LinkedCell = Chr(Shp.TopLeftCell.Column + 64) & CStr(Shp.TopLeftCell.Row)
        End If

        ' Process Events (Keeps GUI Alive)
        DoEvents
    Next

    Application.StatusBar = False
End Sub

While the script runs, progress is indicated in the Application StatusBar in the bottom left corner:

SNAGHTML18fc08c9

Was once an enthusiastic PepperByte employee but is now working elsewhere. His blogs are still valuable to us and we hope to you too.