VBA Express
AfterUpdate event in Class module [Archive] - VBA Express Forum

PDA

View Full Version : AfterUpdate event in Class module



moa
03-01-2007, 08:31 AM
Hi,
I have some classes for controls. My combobox class has an afterUpdate event that doesn't run. Change event works fine but AfterUpdate is just ignored. some of the comboboxes already have afterupdate events (was going to exclude them from the class) would this be the reason why none of the AfterUpdate events fire or is it just not recognized as an event? Doesn't make sense to me.

Class Module:
Option Explicit
Public WithEvents cbCtrls As msforms.ComboBox
Private cmbName As String
Private essential As Boolean

Property Let isEssential(yesNo As Boolean)
essential = yesNo
End Property

Property Get isEssential() As Boolean
isEssential = essential
End Property

Property Let Name(str As String)
cmbName = str
End Property

Property Get Name() As String
Name = cmbName
End Property

Private Sub cbCtrls_AfterUpdate()
MsgBox "Work dammit!"
End Sub

Public Sub Delete()
cbCtrls.Text = ""
End Sub

Initialising:
Public Sub comboBoxClass_Initialise()
Dim ctrl As Control
Dim X As Long
Dim thisCtrl As String
comboCount = 0

For Each ctrl In UserForm1.specFrame.Controls
If TypeName(ctrl) = "ComboBox" Then

thisCtrl = ctrl.Name
comboCount = comboCount + 1

ReDim Preserve combo_box(1 To comboCount)

With combo_box(comboCount)
Set .cbCtrls = ctrl
.Name = thisCtrl

Select Case (thisCtrl)
Case "cmbTSize"
.isEssential = True
Case "cmbPaperType"
.isEssential = True
Case "cmbType"
.isEssential = True
Case Else
.isEssential = False
End Select

End With

End If

Next
End Sub

xld
03-01-2007, 08:42 AM
Glen,

I can't get this code to work. I just run into one error after another.

Can you post your workbook?

moa
03-01-2007, 08:50 AM
No, I really can't. I'll cut the code down.

Standard Module code:
Public Sub comboBoxClass_Initialise()
Dim ctrl As Control
Dim X As Long
Dim thisCtrl As String
Dim comboCount As Long

comboCount = 0

For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "ComboBox" Then

thisCtrl = ctrl.Name
comboCount = comboCount + 1

ReDim Preserve combo_box(1 To comboCount)

With combo_box(comboCount)
Set .cbCtrls = ctrl
.Name = thisCtrl
.isEssential = True
End With

End If

Next
End Sub

moa
03-01-2007, 09:00 AM
I forgot, setting combo_box:
Global combo_box() As New clsComboBoxes

xld
03-01-2007, 09:03 AM
That is where I am getting the problems.

Combo_box is declared, I assumed an array, but this line fails

ReDim Preserve combo_box(1 To comboCount)

and even if I declare it up front, this line fails

Set .cbCtrls = ctrl

what dot properties does an array have?

moa
03-01-2007, 09:12 AM
Cheers for having a look Bob.

Combo_Box is the class instance

xld
03-01-2007, 09:18 AM
Can't get past it, I get an error on this line

ReDim Preserve combo_box(1 To comboCount)

How can you redim a class instance?

moa
03-01-2007, 09:25 AM
Not sure if I'm using the right term (instance).

Global combo_box() As New clsComboBoxes
clsComboBoxes is the name of my class module, the first lot of code in post #1. The second lot of code is in a standard module and it all works fine for me except the AfterUpdate event. When I substitute Change for AfterUpdate the message box shows up after a change in any combo box.

Sorry, don't know a hell of a lot about classes in VBA so I can't say why the redim works (or doesn't in your case).
I'm using Excel 2003 if that explains anything.

xld
03-01-2007, 09:36 AM
Sorry, I am being a bit slow today.

I can see now that Combo_Box is an array of the class instances. I just added a Redim Combo_Box (1 to 1) upfront.

The problem is that the AfterUpdate event isn't exposed in a control array of this type. You can see this if you go into your class, delete that cbCtrls_AfterUpdate procedure, then select cbCtrls from the General dropdown, then select the dropdown from the Declarations list. You will see that there is no AfterUpdate event. Sorry, just can't do it.

moa
03-01-2007, 09:40 AM
Oh well, I can use the Change event so that's good enough. Thanks for your time.