list of vba controls that support automation events.

[oetkbyentc]

Hello,

My question is, has anyone found or compiled a list of controls for vba that support arrays with events?

Im having trouble generating a control array for Text box's and combo box's added at run time. Ive created arrays for command, toggle and spin buttons that support all events associated with those controls with no trouble. But everytime I try it with a text box I get a run time stating that this object doesnt support automation events.

 

[combo]

How did you do it for commandbuttons?
You can't dim withevents variable as Control.
There is no fixed set of events assigned to the control. For instance TextBox has a set of events visible in the object browser. It is available when you define withevents variable as (msforms) textbox. A textbox on the userform has additional set of events inherited from Control. The same textbox on worksheet inherits OLEObject events.

combo

 

[oetkbyentc]

I have attached a sample that shows a small sample of a VBA control array. Copy the code into the locations noted.

A userform named UserForm1:

Private colCalendar_Show As New Collection
Private ctlCalendar As Calendar_Classmodule
Public iButton

Private Sub UserForm_Initialize()

Dim iTop, iLeft

iTop = 2
iLeft = 2

Set Label = UserForm1.Controls.Add("forms.Label.1", "Date")
Label.Top = iTop
Label.Left = iLeft
Label.Width = 65
Label.SpecialEffect = fmSpecialEffectSunken
Label.Font.Name = "Times New Roman"
Label.Font.Size = 10
Label.Font.Italic = False
Label.Font.Bold = False
Label.TextAlign = fmTextAlignCenter
Label.BackColor = &H8000000F
Label.Height = 17
Label.ControlTipText = ""
Label.Caption = "Date"
iLeft = Label.Left + Label.Width + 2

Set Label = UserForm1.Controls.Add("forms.Label.1", "Job_No")
Label.Top = iTop
Label.Left = iLeft
Label.Width = 50
Label.SpecialEffect = fmSpecialEffectSunken
Label.Font.Name = "Times New Roman"
Label.Font.Size = 10
Label.Font.Italic = False
Label.Font.Bold = False
Label.TextAlign = fmTextAlignCenter
Label.BackColor = &H8000000F
Label.Height = 17
Label.ControlTipText = ""
Label.Caption = "Job No."
iLeft = Label.Left + Label.Width + 2
iTop = Label.Top + Label.Height + 2

For i = 1 To iControls
iLeft = 2
Set cbCalendar(i) = UserForm1.Controls.Add("forms.CommandButton.1", "Calendar" & i)
cbCalendar(i).Top = iTop
cbCalendar(i).Left = iLeft
cbCalendar(i).Width = 10
cbCalendar(i).Font.Italic = False
cbCalendar(i).Font.Bold = False
cbCalendar(i).BackColor = &H0&
cbCalendar(i).Height = 17
cbCalendar(i).ControlTipText = "Show Calendar for date selection."
cbCalendar(i).Tag = i
cbCalendar(i).Caption = ""
cbCalendar(i).TabStop = False
iLeft = cbCalendar(i).Left + cbCalendar(i).Width

Set tbDate(i) = UserForm1.Controls.Add("forms.textbox.1", "Date" & i)
tbDate(i).Top = iTop
tbDate(i).Left = iLeft
tbDate(i).Width = 55
tbDate(i).SpecialEffect = fmSpecialEffectSunken
tbDate(i).Font.Name = "Times New Roman"
tbDate(i).Font.Size = 10
tbDate(i).Font.Italic = False
tbDate(i).Font.Bold = False
tbDate(i).TextAlign = fmTextAlignLeft
tbDate(i).BackColor = &H80000005
tbDate(i).Height = 17
tbDate(i).MultiLine = False
tbDate(i).WordWrap = False
tbDate(i).MaxLength = 10
tbDate(i).ControlTipText = ""
tbDate(i).Tag = i
tbDate(i).Locked = False
tbDate(i).TabStop = True
tbDate(i).TabKeyBehavior = False
iLeft = tbDate(i).Left + tbDate(i).Width + 2

Set tbJob_No(i) = UserForm1.Controls.Add("forms.textbox.1", "Job_No" & i)
tbJob_No(i).Top = iTop
tbJob_No(i).Left = iLeft
tbJob_No(i).Width = 50
tbJob_No(i).SpecialEffect = fmSpecialEffectSunken
tbJob_No(i).Font.Name = "Times New Roman"
tbJob_No(i).Font.Size = 10
tbJob_No(i).Font.Italic = False
tbJob_No(i).Font.Bold = False
tbJob_No(i).TextAlign = fmTextAlignLeft
tbJob_No(i).BackColor = &H80000005
tbJob_No(i).Height = 17
tbJob_No(i).MultiLine = False
tbJob_No(i).WordWrap = False
tbJob_No(i).ControlTipText = ""
tbJob_No(i).Tag = i
tbJob_No(i).Locked = False
tbJob_No(i).TabStop = True
iTop = tbJob_No(i).Top + tbJob_No(i).Height + 2
Next i

Calendar_Collect
UserForm1.Show (MODAL)
UserForm1.Repaint

End Sub

Sub Calendar_Set(Button)

iButton = Button
frCalendar.Top = cbCalendar(Button).Top + cbCalendar(Button).Height + 2
frCalendar.Left = tbDate(Button).Left + 3
frCalendar.Visible = True
frCalendar.ZOrder (0)
frCalendar.Calendar1.SetFocus
frCalendar.Repaint

End Sub

Private Sub Calendar1_DblClick()

frCalendar.Visible = False

tbDate(iButton).Value = Calendar1.Value
tbJob_No(iButton).SetFocus

End Sub

Sub Calendar_Collect()

For i = 1 To iControls
Set ctlCalendar = New Calendar_Classmodule
ctlCalendar.Init cbCalendar(i), Me
colCalendar_Show.Add ctlCalendar
Next i

End Sub

A module:

Public cbCalendar() As CommandButton
Public tbDate() 'As TextBox
Public tbJob_No() 'As TextBox
Public iControls

Sub Load_Sample()

iControls = 3

ReDim cbCalendar(1 To iControls) As CommandButton
ReDim tbDate(1 To iControls)
ReDim tbJob_No(1 To iControls)

Load UserForm1

End Sub

A Class module named Calendar_Classmodule

Private WithEvents cbCalendar As CommandButton
Private frmCalendar As UserForm1

Public Sub Init(ctl As CommandButton, frm As UserForm1)

Set cbCalendar = ctl
Set frmCalendar = frm

End Sub

Sub cbCalendar_click()

Call UserForm1.Calendar_Set(cbCalendar.Tag)

End Sub

Private Sub Class_Terminate()

Set cbCalendar = Nothing
Set frmCalendar = Nothing
DoEvents

End Sub

 

[combo]

I see the problem. 'Private WithEvents cbTextBox As TextBox' does not work. This is due to the order of references and hidden objects. If you open object browser and display hidden members, you can find two TextBox classes. The hidden one belongs to excel and describes Forms controls. It does not support automation. The second one is MSForms TextBox. After above declaration vba uses higher priority excel textbox.
To use MSForms TextBos:

Private WithEvents cbTextBox As MSForms.TextBox

combo