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:
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:
Was once an enthusiastic PepperByte employee but is now working elsewhere. His blogs are still valuable to us and we hope to you too.