Solved: How do I run a macro in a form module from a class module

[xltrader100]

[vba]
''''''' in a Class Module:
Public WithEvents classCmdButtons As msforms.CommandButton

Private Sub classCmdButtons_Click()
Application.Run ("Userform1" & "." & classCmdButtons.Name & "Click")
end sub

'''''' in a form module
Private Sub hideFormClick
Me.Hide
End Sub
[/vba]
A CmdButton named hideForm gets clicked and I get an error at the application.run statement saying: The macro 'Userform1.hideFormClick' cannot be found, although this same syntax works from the Immediate window.

How do I make this work?

[Jan Karel Pieterse]

One way: Pass the form to the class.

In the form:
[VBA]Dim cFoo as clsFoo
Set cFoo = New clsFoo
Set clsFoo.Form = Me[/VBA]
In the Class:
[VBA]Private moForm As Object
Public Property Get Form() As Object
Set Form = moForm
End Property
Public Property Set Form(oForm As Object)
Set moForm = oForm
End Property
Private Sub classCmdButtons_Click()
Form.Hide
End Sub
[/VBA]

[xltrader100]

Thanks, Jan

I don't think that would accomplish what I'm trying to do, which is to call the macros that service the (several hundred) cmdButton clicks on a form directly from the Class with just one line of code, and eliminate the usual handlers altogether. To do this I've named all the macros with the same name as the button they serve, but with a suffix tacked on so the control name doesn't conflict with the handling Procedure. In my case I appended "Click".

So if I have 100 CmdButtons, named CB1 thru CB100, then I would name the Subs that handle the button clicks as Sub CB1Click(), Sub CB2Click().. Sub CB100Click(), and when a click event comes in to classCmdButtons, all it has to do is send it blindly on it's way with Application.Run(classCmdButtons.name & "Click"). No listeners required. Am I being too optimistic? This would save me several hundred lines of code if I can get it to work.

[Jan Karel Pieterse]

But this still requires you to have 100 subs, doesn't it?
Could you describe what your 100 buttons need to do?

[Bob Phillips]

To add to Jan Karel's comment, why do you still have click events at all in the form, why aen't you handling this in the class? You have gone to the trouble of creating a control array class, so use it.

[mikerickson]

When I use custom classes to make a bunch of controls do the same thing, if there are a few of those controls that have a little difference, I put the additional code for the special cases in the UF code sheet.

I also find it useful for the custom class to use Parent property. Rather than refering back to a hidding routine in the userform's code module,
[VBA]classCmdButtons.Parent.Hide[/VBA]could be put in the class module.
If some of the buttons are on MultiPages or Frames, a property ParentUF could be added to the class to return the userform rather than the container control.

[xltrader100]

Yes, I still would have the 100 Subs, but if I could call them directly from the Class then (I think) I could cut out one big step. Such as,
[vba]
Sub serviceThisClickedButton(ByVal classCmdButtons as String)
Select Case classCmdButtons
Case Is = "CB1"
Call CB1Procedure
Case Is = "CB2" '
Call CB2Procedure
---etc. ---
[/vba]
I'm trying to eliminate this "central dispatch" function by adopting the naming convention mentioned earlier for the Subs and controls, so the Class would be calling the button Subs directly with the Application.Run statement (if it works), and all the Case statements would go away.

The only click events handled on the form (for now) are for controls that aren't part of the class. So there are separate class modules for CmdButtons, Labels and Images, because there are a lot of each and they fall naturally into functional groups, but the click events for all other controls are handled on the form. I'm now thinking I may not need to break out the separate controls into different classes and could get along with just one class for all controls and handle all click events in the class.

But my immediate problem (and my question) is how to run the form Subs from the class module.

[Bob Phillips]

Take those subs out of the form module, and move them to a standard code module. You cann cll them from the class module easily then.

[xltrader100]

Well, there's a lot of them, and since nearly all of them are used to modifiy other controls and properties on the same form, then having to go back and qualify all the references to refer to them from off form would be huge.

The normal method of calling the Subs using case statements is written and working, but I'd like to get rid of it if I could. And I'd really like to make this technique work so I could use it in other projects.

[Jan Karel Pieterse]

convert them to public subs in the form they become public methods. Then you can use the Form object variable from the class to call those public subs:

In the class: Form.YourPublicSub1

[Bob Phillips]

We're going round the houses here. If the buttons all do different things, the control array class is pointless. If they don't, you should control it all from the class IMO.

[xltrader100]

Ok, as you suggested I've converted the handlers to Public Subs on the form, but I'm still not able to access them from the class. Here's an example.

CmdButton50 gets clicked on Userform1. The click is detected by classCmdButtons, who now wants desperately to tell somebody about it, but the click is handled with the form Sub named "Public Sub CmdButton50Click", and the only way the class can run the handler directly is to construct the name of the handler using concatenation, knowing the name of the clicked button and knowing that the handler is always the button name + "Click". And this is my sticking point, and the reason that I thought that Application.Run might be the way to go. Your suggestion of Form.YourPublicSub1 would work if I didn't have to construct the name of the Sub, but since I do, is there another syntax that would work here? The handler name is being constructed just fine and Application.Run is trying to run the right Sub but it just can't see into the form to find it.

[mikerickson]

I don't understand the problem.

If you want a button handled differently than the class handles it, put the routine for that button in the button's Click event in the userform's code module.

If you have a whole bunch of buttons, all of which are to be handled the same, but differently than (or in addition to) the way the class handles buttons, create a second class for those.

A UF command button can be linked to several classes, and when the button is clicked, the Click event for each of those classes will be run, after that button's Click event (if there is one) in the Userform's code module has run.

Later Edit:
Another approach would be to create a boolean SpecialHandling property of the class.
[VBA]If SpecialHandling Then
Rem do something
End If[/VBA] could be put in your class's Click event where needed. If you need more than two types of buttons SpecialHandling could be a Long variable and Select Case could be used. (Perhaps a text SpecialHandling would be easier for future maintenance)

About the two specific questions in your last post, if you want to know which button is causing the class's click event, classCmdButtons.Name would give you that info.

And if a routine is to be called from another module, that routine should be in a normal module.

I'm working on a project with similar issues; wanting all my controls to do one thing, but some of them to do special things and, after much trashing about with "special case" code, "one class = one function, special cases are handled on their own, outside of the class" got me out of the jungle of code I had come up with.

[xltrader100]

Mike - It isn't a problem of special handling for buttons. All the handlers are written and working fine, and if I use the normal methods of calling them from the class then everything is just peachy.

The point of the question was that in doing it the 'normal' way I'm left with a huge Case statement that runs on for over 300 lines of code. And I know what you're going to say about the practicality of using a class module if all the controls have different handlers anyway. Well they don't ALL have different handlers and there are enough groups with common handling that a class module is still very useful. And once I've got it built then I might as well use it for everything, hence the large size.

But the scheme I'm trying to make work, of calling all the handlers (by name) directly from the class would make that whole Case statement disappear, and I thought that was worth taking a shot at, plus I think this is quite an elegant method (leaving aside the trivial fact that it doesn't seem to work )

[Jan Karel Pieterse]

I suspect having click events behind the form for the special cases and the class events for the more generic ones is the easiest (most transparent) solution.

[Bob Phillips]

Ok, as you suggested I've converted the handlers to Public Subs on the form, but I'm still not able to access them from the class. Here's an example.

CmdButton50 gets clicked on Userform1. The click is detected by classCmdButtons, who now wants desperately to tell somebody about it, but the click is handled with the form Sub named "Public Sub CmdButton50Click", and the only way the class can run the handler directly is to construct the name of the handler using concatenation, knowing the name of the clicked button and knowing that the handler is always the button name + "Click". And this is my sticking point, and the reason that I thought that Application.Run might be the way to go. Your suggestion of Form.YourPublicSub1 would work if I didn't have to construct the name of the Sub, but since I do, is there another syntax that would work here? The handler name is being constructed just fine and Application.Run is trying to run the right Sub but it just can't see into the form to find it.

Then I repeat, if the code for the click event is in the userform, don't set that up in the control array class. If you have many buttons that do the same thing, you can set these up to be managed by the control array class, the others you leave as managed by the userform class.

There is no problem doing that, but it seems perverse to me to have the code in the form and manage the click event from the control array class in order to call the event in the form.

[Jan Karel Pieterse]

I agree entirely Dennis.

[Bob Phillips]

I agree entirely Dennis.

Dennis is Bob, Jan Karel.

[Jan Karel Pieterse]

Dennis is Bob, Jan Karel.

Ouch. Sorry 'bout that.
Never quite liked the idea of nicknames

[Bob Phillips]

No I don't - now. I used to think it was the done thing to use a handle back then when I first registered here, but I always register by my real name now.