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:
  1. Public Enum NewControl

  2.  
  3.     Label = 0

  4.     TextBox = 1

  5.     ComboBox = 2

  6.     ListBox = 3

  7.     CommandButton = 4

  8.     Image = 5

  9.     CheckBox = 6

  10.     OptionButton = 7

  11.     

  12. End Enum

  13.  
  14.  
  15.  
  16. 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)

  17.  
  18. Dim MyControl As OLEObject

  19. Dim MyControlType As String

  20.  
  21. Select Case ControlType

  22.  
  23.     Case 0

  24.         MyControlType = "Forms.Label.1"

  25.         

  26.     Case 1

  27.         MyControlType = "Forms.TextBox.1"

  28.     

  29.     Case 2

  30.         MyControlType = "Forms.ComboBox.1"

  31.     

  32.     Case 3

  33.         MyControlType = "Forms.ListBox.1"

  34.         

  35.     Case 4

  36.         MyControlType = "Forms.CommandButton.1"

  37.     

  38.     Case 5

  39.         MyControlType = "Forms.Image.1"

  40.     

  41.     Case 6

  42.         MyControlType = "Forms.CheckBox.1"

  43.     

  44.     Case 7

  45.         MyControlType = "Forms.OptionButton.1"

  46.     

  47.  
  48. End Select

  49.  
  50. WkSht.OLEObjects.Add ClassType:=MyControlType

  51.  
  52. Set MyControl = WkSht.OLEObjects(WkSht.OLEObjects.Count)

  53.  
  54. MyControl.Left = ControlLeft

  55. MyControl.Top = ControlTop

  56. MyControl.Width = ControlWidth

  57. MyControl.Height = ControlHeight

  58.  
  59. MyControl.Name = ControlName

  60.  
  61. MyControl.Visible = True

  62.  
  63. End Function



To use it, simply type something like:

VB Code:
  1. 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