Can I send event to a class from a UserForm in Excel vba?

[Jo Helge Rorvik]

Hi!
In the sheet I have a table with members, and it is possible to Add new member, change member, delete member amongs others.
To reduce code in modules, my intention was to have a class to control all actions going on in the member sheet.
In 'Microsoft Excel Objects' Sheet4 (Top section in vba VBAProject) added the Event Worksheet_Activate.
In this event I create the class like this:

Option Explicit
Dim member As CMember

Public Sub Worksheet_Activate()
If member Is Nothing Then
Set member = New CMember
End If
End Sub

Sub Btn_New_Member_Click()
If member Is Nothing Then
Set member = New CMember
End If
Call member.NewMember
End Sub
...

Class CMember
'Member operations
Public Enum MemberOper
MOAdd = 1
...
End Enum

Public Sub NewMember()
UF_Member.RegisterCallback(Me)
UF_Member.Tag = MOAdd
UF_Member.Show
End Sub

Public Sub AddMember(arr as variant)
do something...
End Sub

'Form for member
UF_Member:

Public Sub RegisterCallback(member As CMember)
m_member = member
End Sub

Private Sub UserForm_Activate()

If Me.Tag = 1 Then
Call SetupNewMember

Private Sub Cb_Add_Click()
Dim arr() As Variant
...
arr = StoreFormInArray()
Call m_member.AddMember(arr)
Unload Me

I got problem to register the callback back to UserForm from class CMember
The message is : 'Object doesn't support this property or method'
Can somebody help me how to refer to a class from the userform or is there better ways to do things like this??

Thanks In advance
Jo-Helge

 

[Jo Helge Rorvik]

Hi!
The workaround is an extra level on the callstack

Sheet4
Option Explicit
Dim member As CMember

Public Sub Worksheet_Activate()
If member Is Nothing Then
Set member = New CMember
End If
End Sub

Sub Btn_New_Member_Click()
If member Is Nothing Then
Set member = New CMember
End If
Call member.NewMember
End Sub

Sub BtnAddMember(arr() As Variant)
If member Is Nothing Then
Set member = New CMember
End If
Call member.AddMember(arr)
End Sub
...

--------------------------
Class CMember

'Member operations
Public Enum MemberOper
MOAdd = 1
...
End Enum

Public Sub NewMember()
UF_Member.RegisterCallback(Me)
UF_Member.Tag = MOAdd
UF_Member.Show
End Sub

Public Sub AddMember(arr as variant)
do something...
End Sub
...
-----------------
'Form for member
UF_Member:

Private Sub UserForm_Activate()

If Me.Tag = 1 Then
Call SetupNewMember

Private Sub Cb_Add_Click()
Dim arr() As Variant
...
arr = StoreFormInArray()
Call BtnAddMember(arr)
Unload Me

It's a little bit useless to call a sheet4 instead of calling the class directly, but if this is what's needed...

Jo-Helge