WithEvent in Class module not getting variable from Let Property in Excel VBA

Question

I think I'm missing something simple here. Trying to use a variable in a Class module. Let and Get work fine. But if I try to use the variable in a different sub in the Class module I just get a value of 0.

Class Module clsCombobox

Public WithEvents ComboBox As MSForms.ComboBox
Public WithEvents ComboTextBox As MSForms.TextBox

Public Num As Long

Public Property Let Number(Value As Long)
    Num = Value
End Property

Public Property Get Number() As Long
    Number = Num
End Property

Private Sub ComboBox_Change()
    Me.ComboTextBox.Value = Num
    'this gives value of 0
End Sub

Userform Module

Dim obEvents as clsCombobox
Set obEvents = New clsCombobox
obEvents.Number = 52
MsgBox obEvents.Number 'this prints 52

Sub that sets ComboBox

Private Sub GroupCombobox()
Dim i As Long
Dim obEvents As clsCombobox

Set collCombobox = New Collection

For i = 1 To 5
    Set obEvents = New clsCombobox
    Set obEvents.ComboBox = Me.Controls("cbAbility" & i)
    Set obEvents.ComboTextBox = Me.Controls("tbAbility" & i & "Text")
    collCombobox.Add obEvents
Next i

End Sub

For proper encapsulation, Num should be a Private field. — ThunderFrame
– ThunderFrame, Commented Mar 20, 2018 at 22:45
Can you share the code that sets ComboBox and ComboTextBox? — ThunderFrame
– ThunderFrame, Commented Mar 20, 2018 at 22:47
Added the code. The ComboBox_Change sub works well if I set it to = 52 or = Me.ComboBox.ListIndex. It's something about getting that variable. — bigbucky
– bigbucky, Commented Mar 20, 2018 at 22:54
ComboBox_Change is just the event when you select any option in the dropdown list of one of the comboboxes in the collection — bigbucky
– bigbucky, Commented Mar 21, 2018 at 1:12

gembird · Accepted Answer · 2018-03-22 07:53:59Z

3

It doesn't look like you ever set the value. I assume you mean to do that in the loop? Perhaps not with the value of i, but here you can see the idea...

For i = 1 To 5
    Set obEvents = New clsCombobox

    'Set the value here
    obEvents.Number = 52 ' 52 or whatever is needed as Number

    Set obEvents.ComboBox = Me.Controls("cbAbility" & i)
    Set obEvents.ComboTextBox = Me.Controls("tbAbility" & i & "Text")
    collCombobox.Add obEvents
Next i

edited Mar 22, 2018 at 7:53

gembird

14.1k5 gold badges39 silver badges54 bronze badges

answered Mar 20, 2018 at 23:12

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

bigbucky Over a year ago

The i is just for the loop to add a number of controls to the collection. So cbAbility1, cbAbility2 are different comboboxes. The class allows them all to act the same way when you select something in their dropdown list.

gembird Over a year ago

@bigbucky Maybe you simply forgot obEvents.Number = 52 inside of the for i=1 to 5 loop? When you don't set the Number then it will still have value of zero. @thuderframe you mean Number not Value?

bigbucky Over a year ago

I see. I did t realize I had to set it for each control. I thought once you set the variable it would stay set in the Class module. I’m away from computer but will try later.

gembird Over a year ago

No, each object is independent from the other objects, it is different memory location, each object hast its own state and data etc.

gembird Over a year ago

The class module is like a template for the objects which is created at design time. At runtime the objects are created based on this template and each object is isolated, lives in another memory location.

|

Collectives™ on Stack Overflow

WithEvent in Class module not getting variable from Let Property in Excel VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related