Setting up a class to handle textboxes in a vba userform

[corentint]

Hello all,
I am trying to educate myself about class modules. I have this exemple from John Walkenbach (the code in blue - works fine).
I am trying to do this for a group of textboxes. But somehow, it does not work for me (code in red below).
Read on, if anyone has a solution, I would appreciated very much.
Thanks in advance.

From userform2 (just a userform with 7 textboxes and an Exit command:		From userform1 (just a userform with 16 command buttons
Dim Tboxes() As New TxtClass		Dim Buttons() As New BtnClass
Private Sub Exit2_Click()		Private Sub OKButton_Click()
Unload Me		Unload Me
End Sub		End Sub
Private Sub UserForm_Initialize()		Private Sub UserForm_Initialize()
Dim TboxCount As Integer		Dim ButtonCount As Integer
Dim ctl As Control		Dim ctl As Control
' Create the Textbox objects		' Create the Button objects
TboxCount = 0		ButtonCount = 0
For Each ctl In UserForm2.Controls		For Each ctl In UserForm1.Controls
If TypeName(ctl) = "TextBox" Then		If TypeName(ctl) = "CommandButton" Then
TboxCount = TboxCount + 1		If ctl.Name <> "OKButton" Then 'Skip the OKButton
		ButtonCount = ButtonCount + 1
ReDim Preserve Tboxes(1 To TboxCount)		ReDim Preserve Buttons(1 To ButtonCount)
Set Tboxes(TboxCount).TextboxGroupe = ctl		Set Buttons(ButtonCount).ButtonGroup = ctl
		End If
End If		End If
Next ctl		Next ctl
End Sub		End Sub
From the Class module (named: TxtClass):		From the Class module (named : BtnClass):
Public WithEvents TextboxGroupe As msforms.textbox this statement looks amiss w/b: MSForms,TextBox as per VBE		Public WithEvents ButtonGroup As CommandButton
Private Sub TextboxGroupe_BeforeUpdate(ByVal cancel As msforms.ReturnBoolean)		Private Sub ButtonGroup_Click()
Dim Msg As String		Dim Msg As String
Msg = TextboxGroupe.Name & " was updated by the user"		Msg = "You clicked " & ButtonGroup.Name & vbCrLf & vbCrLf
Msg = Msg & "Caption: " & TextboxGroupe.Name & vbCrLf		Msg = Msg & "Caption: " & ButtonGroup.Caption & vbCrLf
Msg = Msg & "Left Position: " & TextboxGroupe.Left & vbCrLf		Msg = Msg & "Left Position: " & ButtonGroup.Left & vbCrLf
Msg = Msg & "Top Position: " & TextboxGroupe.Top		Msg = Msg & "Top Position: " & ButtonGroup.Top
MsgBox Msg, vbInformation, TextboxGroupe.Name		MsgBox Msg, vbInformation, ButtonGroup.Name
End Sub		End Sub
The only other procedure in one normal module is a call to display the Userform1.		ditto with userform2
This programme does nothing. Not error.
But I found out that the events BeforeUpdate or AfterUpdate simply don't fire up; the event "change" does work, but that is hardly usable.
Why?		The above procedure works fine...
'The text in blue, parallel to this red one, is the same procedure to group command buttons.

 

[Micron]

Interesting method of posting code. I would have liked to copy/paste and try it out but am not going to try to copy and separate one column from the other. Unless someone else easily spots the problem (e.g. perhaps it is TextboxGroupe As Textbox) I can only suggest that in the absence of a quick solution, consider posting the code in vba code tags (vba button on posting toolbar) in order to give responders something they can work with.

 

[corentint]

OK sorry about that. Got carried over.
Here is my code:
USERFORM2

Option Explicit

Dim Tboxes() As New TxtClass

Private Sub Exit2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim TboxCount As Integer
    Dim ctl As Control
'  Create the Textbox objects
    TboxCount = 0
    For Each ctl In UserForm2.Controls
        If TypeName(ctl) = "TextBox" Then
                TboxCount = TboxCount + 1
                ReDim Preserve Tboxes(1 To TboxCount)
                Set Tboxes(TboxCount).TextboxGroupe = ctl
        End If
    Next ctl
End Sub

and:
CLASS MODULE

Public WithEvents TextboxGroupe As msforms.textbox
Private Sub TextboxGroupe_BeforeUpdate(ByVal cancel As msforms.ReturnBoolean)
    Dim Msg As String
    Msg = TextboxGroupe.Name & " was updated by the user"
    Msg = Msg & "Caption: " & TextboxGroupe.Name & vbCrLf
    Msg = Msg & "Left Position: " & TextboxGroupe.Left & vbCrLf
    Msg = Msg & "Top Position: " & TextboxGroupe.Top
    MsgBox Msg, vbInformation, TextboxGroupe.Name
End Sub

============================================================
Then here is the code that works:

USERFORM1

Option Explicit
Private Sub UserForm_Initialize()
    Dim ButtonCount As Integer
    Dim ctl As Control  
' Create the Button objects
    ButtonCount = 0
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "CommandButton" Then
            If ctl.Name <> "OKButton" Then 'Skip the OKButton
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
                Set Buttons(ButtonCount).ButtonGroup = ctl
            End If
        End If
    Next ctl
End Sub

Private Sub OKButton_Click()
    Unload Me
End Sub

and:

CLASS MODULE

Public WithEvents ButtonGroup As CommandButton
Private Sub ButtonGroup_Click()
    Dim Msg As String
    Msg = "You clicked " & ButtonGroup.Name & vbCrLf & vbCrLf
    Msg = Msg & "Caption: " & ButtonGroup.Caption & vbCrLf
    Msg = Msg & "Left Position: " & ButtonGroup.Left & vbCrLf
    Msg = Msg & "Top Position: " & ButtonGroup.Top
    MsgBox Msg, vbInformation, ButtonGroup.Name
End Sub

 

[Jaafar Tribak]

The MsForms TextBox Class doesn't support the _BeforeUpdate event, nor does it support the _Enter, _Exit and _AfterUpdate events.
AFAIK, Those events can only be sinked in a class module via low level shlwapi function calls or via the IConnectionPoint interface

Public WithEvents TextboxGroupe As MSForms.Control does add the signature of those event handlers to the VBIDE but doesn't
work.

For an example of how to use the ConnectToConnectionPoint api to sink those events, please take a look here:

Check textbox for future date

Good day again I would like to when user has entered a date (in any format) inside a textbox that when exiting the textbox it checks whether the date is in the future and then displays a message. Also would it be then able to when exiting that the date be changed to show as format 12-Mar-20...

www.mrexcel.com

 

[dmt32]

The MsForms TextBox Class doesn't support the _BeforeUpdate event, nor does it support the _Enter, _Exit and _AfterUpdate events.

seem to recall that you developed a neat solution for some of the Enter & Exit events in past

its a long thread - #Post 28 onward

Cannot SetFocus > userform control

Hi All Posting a rather lengthy class here. Everything works as expected, except the .SetFocus line. Testing on textboxes, the control in question is not selected, yet the error message displays: clsFormsControlsEvents: Private WithEvents m_TextBoxEvents As MSForms.TextBox Private WithEvents...

www.mrexcel.com

Dave

 

[corentint]

OK, I can work with Jaafar Tribak solution using the IConnectionPoint interface although how this codes actually works is way beyond me. But I made it work in a simple userform with 6 text boxes, and I can even easily include or exclude textboxes with the .name property. SO thank you.

As to why the Change event of Textbox should work and not the other events, seems inconsistent to me.

Thank you all.

 

[Jaafar Tribak]

As to why the Change event of Textbox should work and not the other events, seems inconsistent to me.

The other events are exposed by the ControlEvents interface.
WithEvents TextboxGroupe As MSForms.Control *should*, in theory work, but it doesn't.

 

[corentint]

Well thank you for this information and all your help. Have a good day.