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:
Public Enum NewControl
Label = 0
TextBox = 1
ComboBox = 2
ListBox = 3
CommandButton = 4
Image = 5
CheckBox = 6
OptionButton = 7
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
MyControlType = "Forms.Label.1"
MyControlType = "Forms.TextBox.1"
MyControlType = "Forms.ComboBox.1"
MyControlType = "Forms.ListBox.1"
MyControlType = "Forms.CommandButton.1"
MyControlType = "Forms.Image.1"
MyControlType = "Forms.CheckBox.1"
MyControlType = "Forms.OptionButton.1"
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
To use it, simply type something like:
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.