Dynamically add controls in Office

Often it is handy to be able to dynamically add controls to Office documents, particularly spreadsheets, on the fly. However, Office doesn't support control arrays in the same way as VB6.

So, to assist in this useful and oft-requested task, here is some code:

VB Code:

Public Enum NewControl
 
    Label = 0
    TextBox = 1
    ComboBox = 2
    ListBox = 3
    CommandButton = 4
    Image = 5
    CheckBox = 6
    OptionButton = 7
    
End Enum
 
 
 
Public Function AddControl(ByVal WkSht As Worksheet, ByVal ControlType As NewControl, ByVal ControlName As String, ByVal ControlLeft As Double, ByVal ControlTop As Double, ByVal ControlWidth As Double, ByVal ControlHeight As Double)
 
Dim MyControl As OLEObject
Dim MyControlType As String
 
Select Case ControlType
 
    Case 0
        MyControlType = "Forms.Label.1"
        
    Case 1
        MyControlType = "Forms.TextBox.1"
    
    Case 2
        MyControlType = "Forms.ComboBox.1"
    
    Case 3
        MyControlType = "Forms.ListBox.1"
        
    Case 4
        MyControlType = "Forms.CommandButton.1"
    
    Case 5
        MyControlType = "Forms.Image.1"
    
    Case 6
        MyControlType = "Forms.CheckBox.1"
    
    Case 7
        MyControlType = "Forms.OptionButton.1"
    
 
End Select
 
WkSht.OLEObjects.Add ClassType:=MyControlType
 
Set MyControl = WkSht.OLEObjects(WkSht.OLEObjects.Count)
 
MyControl.Left = ControlLeft
MyControl.Top = ControlTop
MyControl.Width = ControlWidth
MyControl.Height = ControlHeight
 
MyControl.Name = ControlName
 
MyControl.Visible = True
 
End Function

To use it, simply type something like:

VB Code:

AddControl Worksheets(1), Label, "MyNewLabel", 100, 100, 200, 100

You can add any of the other controls to the list just by continuing the Enum in the same style as I have done. And of course you can add other parameters such as the Caption, Value (for checkboxes and options), image (for images) into the function.
To create a pseudo-array, simply call the function in a loop and pass the name of the control with an index incorporated.

You could couple it with Declan's Event Handler code to allow full flexibility.


zaza