Form events using set property

[Fredrik1987]

Hi!

I'm trying to assign events to the text- and comboboxes in a userform.
It works fine as long as I just add one of the control types (TextBox or ComboBox), or assign each control to a different property name.The problem occurs when I'm assigning both Textbox and ComboBox to the same in cFormEvents (ControlProperty)

Form name = uLeggTilFerie
Module name = mFormEvents
Class module name = cFormEvents

Here's the code in mFormEvents:

Option Explicit


'/  Control and events
Public Control As MSForms.Control
Private TextBoxEvent As cFormEvents
Private ComboBoxEvent As cFormEvents
'/  Collections
Private TextBoxCollection As Collection
Private ComboBoxCollection As Collection


Sub SetProperty()
'/==================================================================================================
'/  Set Control events
'/==================================================================================================
Set TextBoxCollection = New Collection
Set ComboBoxCollection = New Collection


'/  Loop throug each control in userform and asign property to each control
For Each Control In uLeggTilFerie.Controls
    '/  TextBox events
    If TypeOf Control Is MSForms.TextBox Then
        '/ Compile Error: Argument not optional
        Set TextBoxEvent = New cFormEvents
        Set TextBoxEvent.ControlProperty = Control
        TextBoxCollection.Add TextBoxEvent
    '/  ComboBox events
    ElseIf TypeOf Control Is MSForms.ComboBox Then
        Set ComboBoxEvent = New cFormEvents
        Set ComboBoxEvent.ControlProperty = Control
        ComboBoxCollection.Add ComboBoxEvent
    End If
Next Control


Set TextBoxEvent = Nothing
Set ComboBoxEvent = Nothing
End Sub

Code in cFormEvents

Option Explicit


Private WithEvents MyTextBox As MSForms.TextBox
Private WithEvents MyComboBox As MSForms.ComboBox
Private WithEvents MyButton As MSForms.CommandButton


Public Property Set ControlProperty(TB As MSForms.TextBox, CB As MSForms.ComboBox)


Set MyTextBox = TB
Set MyComboBox = CB


End Property

I'm guessing the problem is my lack of knowlegde regarding properties and class modules

 

[RickXL]

Hi,

The controls will automatically have events assigned to them there is no need to use a Class.

The other problem is that when you Set or Let a Property you would usually only have one argument. If you specify two then you have to use two.

After you have added the Controls to your UserForm you should be able to see the Events in the UserForm code module by looking at the drop-downs at the top of the edit window.

 

[Fredrik1987]

Thank you so much for your help! I'll just create one property for each control
Got a bit stuck withthe possibility to set several arguments in a property....

 

[Jaafar Tribak]

You could do something like this without the need to create one property for each control :

In the form module :

Private CtrlCollection As Collection

Sub SetProperty()
    '/==================================================================================================
    '/  Set Control events
    '/==================================================================================================
    Dim Control As Control
    Dim ControlEvents As cFormEvents
    
    Set CtrlCollection = New Collection
    For Each Control In Me.Controls
        If TypeOf Control Is MSForms.TextBox Or TypeOf Control Is MSForms.ComboBox Or _
        TypeOf Control Is MSForms.CommandButton Then
            Set ControlEvents = New cFormEvents
            Set ControlEvents.ControlProperty = Control
            CtrlCollection.Add ControlEvents
        End If
    Next Control
End Sub

In the Class module:

Private WithEvents MyTextBox As MSForms.TextBox
Private WithEvents MyComboBox As MSForms.ComboBox
Private WithEvents MyButton As MSForms.CommandButton


Public Property Set ControlProperty(Ctrl As Control)
    Select Case True
        Case TypeOf Ctrl Is MSForms.TextBox
            Set MyTextBox = Ctrl
        Case TypeOf Ctrl Is MSForms.ComboBox
            Set MyComboBox = Ctrl
        Case TypeOf Ctrl Is MSForms.CommandButton
            Set MyButton = Ctrl
    End Select
End Property

 

[RickXL]

You could do something like this without the need to create one property for each control

OK, I understand now.

If that is what you want to do then go for Jaafar's solution. I might be tempted to replace

Sub SetProperty()

with

Sub UserForm_Initialize()

to save having to call it explicitly. Then you will be able to add more generic events to the Class Module. For example:

Private WithEvents MyTextBox   As MSForms.TextBox
Private WithEvents MyComboBox  As MSForms.ComboBox
Private WithEvents MyButton    As MSForms.CommandButton

Public Property Set ControlProperty(Ctrl As Control)
    Select Case True
        Case TypeOf Ctrl Is MSForms.TextBox
            Set MyTextBox = Ctrl
        Case TypeOf Ctrl Is MSForms.ComboBox
            Set MyComboBox = Ctrl
        Case TypeOf Ctrl Is MSForms.CommandButton
            Set MyButton = Ctrl
    End Select
End Property

Private Sub MyTextBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Debug.Print MyTextBox.Text
End Sub

shows how to add a TextBox double-click event to the code that will run for all TextBoxes.

Regards,

 

[Fredrik1987]

Thank you so much both of you! This is exactly what i wanted!
Another quick question about naming convention (If it's not to much trouble): I'm trying to avoid having any code behind the userform, so my script follows these steps.

1. A button in a worksheet calls a module (mFormCreate) that inserts labels, multipages etc. into the userform
2. mFormCreate calls SetProperty, which connects the form events in cFormEvents

Is this considered bad practice, I read somewhere that you shoul avoid putting to much code behind a userform?
I'm trying to transition into Visual Studio Code, read somewhere (again) that it was difficult exoprting code behind a userform since it was considered "code behind a code"?

I'm sorry if the question is unclear, nevertheless, thank you so much for your help!!

 

[RickXL]

I am probably not the best person to advise. I did write a Visual Studio Excel program once but only to answer a question here. I am sure that not knowing about the higher level does hinder my understanding of VBA.

Certainly for VBA, I would place all the code specifically related to a UserForm in the UserForm Module just as I would place all the Worksheet-specific code in a Worksheet Module. If you had some code that could be called by more than one UserForm I would probably place it in a standard Code Module if it worked there.

UserForms are slightly strange in VBA. They are Class Modules that have had the GUI part added.

 

[Fredrik1987]

Thank you so much for the help!
As you said, the code should be placed in its own moduel if its called by several forms (which is the case here )

 

[RickXL]

No problem and thanks for the feedback.

The main thanks must go to Jaafar, though.

Regards,