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