Call a named Userform Procedure using a variable from a Class

[gmaxey]

I'm nearly completely out of my element when it comes to employing custom classes in a project. However, I've manage to cobble together a class that serves primarily to trigger multiple userform control events (e.g., Textbox1_Change, ComboBox1_Change etct.)

All of the events call a common procedure in the class which in turn calls a procedure in the userform for example:




Sub txt_Change()
Change_Validate txt
End Sub

Sub Change_Validate(oEventCtrl As Object)
'Receives the event control.
'Passes the event control to the userform validation procedure.
fcnParentForm(oEventCtrl).Change_ValidateControl_Form oEventCtrl
'Looking for a way to replace the line above with something like:
fcnParentForm(oEventCtrl). 'the name of validation procedure passed to the class from the userform
lbl_Exit:
Exit Sub
End Sub


Function fcnParentForm(oEventCtrl As Object) As Object
Dim oUserForm
'Returns the control's parent userform.
Set oUserForm = oEventCtrl.Parent
Do While TypeOf oUserForm Is MSForms.Control
Set oUserForm = oUserForm.Parent
Loop
Set fcnParentForm = oUserForm
lbl_Exit:
Exit Function
End Function



It is working well and the only thing annoying is that to use the class, the developer has name the central validation procedure in the UserForm to match the hard code above i.e., it must be named "Change_ValidateControl_Form"

I am trying to figure out (an over my head) how to be able to pass (from the userform) and store in the class the name of the procedure to run when the class object is created. I have figured out how to pass the name of the procedure to run to a property of the class but can't figue out how to actually call:

fcnParentForm(oEventCtrl).(The procedure name property) oEventCtrl

Thanks for reading and any suggestions or help provided.

[SamT]

Greg, I am not sure what you are trying to do. It looks (to me, anyway,) as if you are trying to intercept the Events from a UserForm, then run certain Subs and Functions in that same UserForm,depending on the UserForm Event that occurred. I know that is not what you are doing, because, why add an invisible and redundant layer to the Developer's code load?


I am trying to figure out (an over my head) how to be able to pass (from the userform) and store in the class the name of the procedure to run when the class object is created.

UserForm Developer's Code

Option Explicit

Dim MyMaxey As New ClsGMaxey

Private Sub UserForm_Initialize()
Set MyMaxey.DevelopersUserForm = Me

With MyMaxey.ChangeEvent_ControlProcedurePairs
.Add Me.Txt1, "Sub1"
.Add Me.Txt2, "Sub2"
.Add Me.Txt3, "Sub3"
End With

With MyMaxey.ClickEvent_ControlProcedurePairs
.Add Me.Txt1, "Sub7"
.Add Me.Txt2, "Sub8"
.Add Me.Txt3, "Sub9"
End With
'etc, et al.
End Sub

[Paul_Hossler]

Can you post a simple DOTM with the class and UF?

[gmaxey]

Paul,
Uploaded. I'll seen you a password via PM for the project because it is not ready for public viewing.

[gmaxey]

SamT/Paul

I've uploaded a simplified demo (unlocked).

Sam, yes in a certain sense, I am using a Class to create and trigger UserForm control events then run a specific named sub in that same UserForm.

To work, that UserForm sub must be named "Change_ValidateControl_Form"

Change_ValidateControl_Form in the UserForm can be as simple as the example in the attached where all UserForm txtboxes are validated for basic data entry (any text) or very specific.

The example has about a dozen textboxes. A dozen individual Textbox change events in the Userform wouldn't be so bad but a 100 or 1000 would be ;-)

I am trying to figure out how to make the Class call whatever the Userform designer wants it to call and not make the Userform designer have to name the called procedure according to the hard coded name in the Class.

I hope that makes sense, but as I've already admitted I am out of my element here.

[SamT]

I am probably not understanding what you're doing, so feel free to ignore this post.

I think a brilliant person had a brilliant idea, but forgot to "Roll a Sanity-Check" before running with his idea.

Not that that has ever happened to me, of course.

How does this save the developer time and effort???

[gmaxey]

Sam

If I import my class into a project with a Userform then I can have 1 or 1000 textboxes in that form without having to have a specific "TextBox1_Change", TextBox2_Chage", etc. event.

Wouldn't not having to create those specific events save time?

If you look at the demo you will probably see what is going on. After doing so you might ask, "Why not validate the form in the Class as well?" I could in the demo case because the validation is basic. However by calling a validation procedure in the form the validation can be specific e.g,

Select Case oEventCtrl.Name
Case "txtPhoneNumber"
'Validate a phone number"
Case Else
'General validation
End Select

The Class code is static. Tweaking is done in the form.

[SamT]

The Class code is static. Tweaking is done in the form.
That's my point. Well that and the fact that in two or three clicks of the mouse and I have a Stub for any Control Event.

Example:

Private Sub txtNameFirst_Change()
'Two mouse clicks to generate this Stub
'Select, Ctrl+C to copy "txtNameFirst"
'Hand type "GeneralValidation Me." then Ctrl+V
GeneralValidation Me.txtNameFirst
End Sub
For TextBox txtNameLast, Copy the above sub and edit "First" to "Last" twice.

Compare to:

Private Sub UserForm_Initialize()
'Three mouse clicks to generate this Stub
'Hand type all the rest
Set MyMaxey.DevelopersUserForm = Me

With MyMaxey.ChangeEvent_ControlProcedurePairs
.Add Me.Txt1, "Sub1"
'Select and Ctrl+C to copy "Add Me." for all next "Adds"


Enough kibitzing.


Class Modules are blueprints for custom Objects, In your case, a UserFormEventHandler Object. Being as I'm a lazy Typist, hereinafter a UFEH Object.

So... What are the requirements for a UFEH?


Must handle Many Events for Many Controls

Must be able to call Many UF Subs for Many Events For Many Controls.



Properties For many Events Of Many Controls:


Control (UFEH Property)

EventType (UFEH Property)

Response (UHEF Method AKA UF Sub to call)


EventType

Method



Control

EventType

Method


EventType

Method






Have you Written any User Scenarios yet? Have you made an Object Model yet? Can you describe, in English, what your UFEH will do for me, a Developer? Can you tell me how to use it?

It's really hard to think about coding an Object without knowing quite a bit about the Object

[gmaxey]

SamT,

I don' really see the point in continuing our dialog. I gave you an example and an explanation. Of course you can look at it or dismiss it as you wish. The question is pretty clear (like you say "at least to me"). In a Class procedure can hard coded:


Userform.ThisNamedMaro


Be replaced with:

UserForm.RunTheNameMacroDefinedBySomePropertyOfTheClass

Maybe there isn't an answer. Thanks anyway.

[SamT]

I don' really see the point in continuing our dialogOK :friends: :beerchug:

[Paul_Hossler]

I took a slightly different approach and mocked up a proof of concept macro for you to possibly consider

IMVVVHO, I think it'd be easier to have a class With Events for each type of control (TextBox, OptionButton, etc.) so clsTextBox, clsOptionButton

When initializing the UF, the UF controls' classes above are Set into a Collection based on the TypeName so all TextBoxes share the same event handler code, same validation rule set (different values, etc)

If you're interested single step through the attachment; it will explain much better

I only did two control types (2 TB's and 2 OB's) and only a TB Change event so when you enter something into TB1 and into TB2 the same event handler code is used.

I did add validation to the 2 classes so in my UF Init code you can see where I said TB1 has to be 5 or more char, TB2 has to be a number, etc.

[gmaxey]

Paul,

Bleary eye from another unrelated task. I'll look this over. Thanks.

BREAK

SamT. Still friends? Of course.

[SamT]

Of course. Just 'cuz I'm blind to your vision doesn't affect that.

[gmaxey]

Paul,

Interesting approach and thanks for sharing! It certainly works as designed and I can see its usefulness.

I do see that to create validate a new unique condition then you or the user of your class would have to add or modify code in the class. For example if wanted to validate a frame to see that only a maximum of 3 out of 5 checkboxes are checked then you would need to modify your class.

That is what I am hoping to avoid. I want a class that is complete and to use it for a variety of purposes, the user changes code in their form.

Oddly enough, if I declare the oDict Public in a standard module and move the validation routine and function to a standard module I can then call
the validation procedure from the class using Application.Run "strMacroName"

However, for whatever reason that won't work if the procedure is in a UserForm.

Oh well. I suppose it is what it is.

[Paul_Hossler]

Oh well. I suppose it is what it is.

I believe that is Word's Motto




I do see that to create validate a new unique condition then you or the user of your class would have to add or modify code in the class. For example if wanted to validate a frame to see that only a maximum of 3 out of 5 checkboxes are checked then you would need to modify your class. That is what I am hoping to avoid. I want a class that is complete and to use it for a variety of purposes

Yes, the class clsFrame would need to be modified (programmer task) to add a new condition type, such as min/max number of selected CheckBoxes in a Frame control, BUT the user would only need to use the condition type in the UF module

Using what I had learned for an earlier project, I'd prefer to modularize (objectize??) and have a repertoire of enhanced UF Control objects (clsEnhTextBox, etc.) with the right properties and events and etc. instead of a monolithic UF 'class' (I actually do have a clsUserForm but it just has UF [i.e. no Control] related methods and properties).



the user changes code in their form.

Well .... do you think that the complexity would be over whelming to the typical VBA-literate user/macro writer?

You could have a lot of properties and options exposed, or maybe just expose the underlying object to allow the user (configure??) to values (MyTextboxs(1).enhTextbox.BackColor = ....)

IIRC there's a 64K limit on module size, and after that it starts to become unreliable



Well, here another example if you want to consider more specialized objects. I used your simplified example, but never did the validation part (too much work if you weren't interested) but maybe there's some ideas you can use

[gmaxey]

Paul, Thanks I'll look that over ASAP.

[SamT]

Greg,

Oddly enough, if I declare the oDict Public in a standard module and move the validation routine and function to a standard module I can then call
the validation procedure from the class using Application.Run "strMacroName"

However, for whatever reason that won't work if the procedure is in a UserForm.

Have you tried setting a Class variable to the UserForm, then

mUsrFrm.Run "strMacroName"

[gmaxey]

SamT,

No joy. Same error type.

[gmaxey]

EUREKA!!!!

CallByName is the key to the kingdom:


Sub Change_Validate(oEventCtrl As Object)
'Receives the event control.
'Passes the event control to the userform validation procedure.
'Here"ValidationProcedure" is a Class property defined in the userform when the class object is created.

CallByName fcnParentForm(oEventCtrl), ValidationProcedure, VbMethod, oEventCtrl
lbl_Exit:
Exit Sub
End Sub

Function fcnParentForm(oEventCtrl As Object) As Object
Dim oUserForm
Set oUserForm = oEventCtrl.Parent
Do While TypeOf oUserForm Is MSForms.Control
Set oUserForm = oUserForm.Parent
Loop
Set fcnParentForm = oUserForm
lbl_Exit:
Exit Function
End Function

[SamT]

:mbounce:

[dj44]

:2jump:


I am happy to add my 2 bounces - although i have no idea about classes and often fail to make my userform run
Well done a problem solved is a problem no more



:grinhalo:

[gmaxey]

Attached is the working demo for anyone who may be interested.

[Paul_Hossler]

Glad you solved it

Thanks for the upload of the finished product


Q: So if I want to use it, I'd

a. Create the UF with controls, etc.

b. Insert your clsFormControlEvents into the project

c. Write or customize 3 userform subs and function

Sub InitializeValidationAndPrompts(),
Public Sub Change_ValidateControl_Form(oCtrl As Object)
Private Function fcnValidate(oCtrl As Object) As Boolean

Thereafter, your 'classy' class does all the heavy lifting

[gmaxey]

Paul (and SamT too)

Thank you! Your methods give me something to think about. As for your Q above, yes. That about sums it up. Of course the class at this stage was built to handle the demo. The demo events "Cick" and "Change" "SpinUp/Down" are about the only one I ever use but I can add more (and so can you).

The prompt piece is builds upon previous work shared with me by Christopher Mackay a few months ago.

I had come across the CallByName function before when reading through VBA help but the example MS has published doesn't work as written and after fixing it I wandered about the point as the same thing can be done much easier (or seemed to me) without using it.

SamT's suggestions about setting the form as an object (which I think I had already done in a round about way) stirred a dim recollection of CallByName and the rest is history.

I've attached a document showing the basic MS example and why I thought it was pointless and showing how the Application.Run falls over and can be overcome.

[SamT]

I was working on something a couple of months ago where I had to set up two way communication between 2 or more User Defined Objects. I figgered it should work with a UserForm Object.

Unfortunately, I didn't know about CallByName then.