About Virtualization, VDI, SBC, Application Compatibility and anything else I feel like
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.
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)
' Process Events (Keeps GUI Alive)
Application.StatusBar = False
While the script runs, progress is indicated in the Application StatusBar in the bottom left corner:
.NET .NET FrameWork Active Directory Altiris Apple Automation Manager Citrix Dell Delphi Excel Exchange Exchange2003 Exchange2010 Hack HP iOS Java LinkedIn Linux Lync MSI Office Office 2010 Passat Password PowerPoint PowerShell RES RNS510 SasLibEx SCOM Security Terminal Server ThinApp TSAdminEx VBS VCDS Visual Basic Visual Studio VMWare Volkswagen VW Windows PE Wordpress XenApp