VBA Classes

[Jameo]

Hi all,

I am attempting to use some classes to group together a number of option and check boxes on my forms.

Does anyone have a link handy with an introduction explaining their usage? I have found some examples of code in various places, but would ideally like a little bit of a narritive along with the code.

Many thanks

 

[Sektor]

Watch here.
You want to create common event handler?

 

[Jameo]

To be honest, I have very little understanding of the class structure, I am still relatively new to VBA.

I basically want to assign some common code to some of the check boxes in my userform. i.e. every time one of the check boxes is modified, the code would run for all the check boxes. It could be something simple like updating all of the variables assigned to each check box.

When looking around for how to do this, the common technique seemed to be to asign all of the objects into a class, and run the code for that class.

I am really trying to just get a basic understanding of how the code works. For instance, an example of the code i have been looking at is:

For Each Ctrl In Simulation2Form.Controls
    If TypeName(Ctrl) = "TextBox" Then
        TBCount = TBCount + 1
        ReDim Preserve TBs(1 To TBCount)
        Set TBs(TBCount).TBGroup = Ctrl
    End If
Next Ctrl

By the way, thanks for the link. I am struggling through the information there as we speak.

 

[Sektor]

Here's example of using classes. Ask if you don't understand something.

 

[Jameo]

Grrrr.

Unfortunately the filter at work is blocking that link for me Sektor, I will have to take a look when I get home this evening. Thanks for your time

 

[Sektor]

In ThisWorkbook module.

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Workbook_Open()
    
    [COLOR="Blue"]Dim[/COLOR] ole [COLOR="Blue"]As[/COLOR] OLEObject
    [COLOR="Blue"]Set[/COLOR] col = [COLOR="Blue"]New[/COLOR] Collection
    
    ' This loop connects all buttons on menu sheet
    ' to one event hanlder in EventsTrapper class.
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] ole [COLOR="Blue"]In[/COLOR] Sheets("menu").OLEObjects
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]TypeOf[/COLOR] ole.Object [COLOR="Blue"]Is[/COLOR] MSForms.CommandButton [COLOR="Blue"]Then[/COLOR]
            [COLOR="Blue"]Set[/COLOR] EventTrapper = [COLOR="Blue"]New[/COLOR] EventsTrapper
            [COLOR="Blue"]Set[/COLOR] EventTrapper.Button = ole.Object
            col.Add EventTrapper
            [COLOR="Blue"]Set[/COLOR] EventTrapper = [COLOR="Blue"]Nothing[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

In standard module:

[COLOR="Blue"]Public[/COLOR] col [COLOR="Blue"]As[/COLOR] Collection
[COLOR="Blue"]Public[/COLOR] EventTrapper [COLOR="Blue"]As[/COLOR] EventsTrapper

In class module:

[COLOR="Blue"]Option[/COLOR] [COLOR="Blue"]Explicit[/COLOR]

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]WithEvents[/COLOR] Button [COLOR="Blue"]As[/COLOR] MSForms.CommandButton

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Button_Click()
[COLOR="Green"]	' Handling button's Click event....[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Jameo]

Thanks, mind if I ask a few questions about the above?

I know little about OLE objects, other than it stands for Object Linking and Embedding, and is a way to share data between applications.

Going to the first instance of code you posted, as I understand it,

I guess that it first looks for any OLEObject that is a Command Button, (don't know why it is looking in the "menu" sheet). After finding one, it sets an event trapper for that button, names the event as "Button" and then adds to the col collection.

After the code has run, it should have all the command buttons as a collection, that can be ran with the event "Button"

Public col As Collection
Public EventTrapper As EventsTrapper

Simply defines the collection and event trapper name

Finally,

Option Explicit
 
Public WithEvents Button As MSForms.CommandButton

Defines the "Button" Event

Private Sub Button_Click()
    ' Handling button's Click event....
End Sub

Defines an action when any commandbutton, listed in the Button event, from the col collection is clicked.

Is that about right? Thsi is first time I have really encoutered the above code, so most fo this is guesswork.

Thanks for your help, really appreciated.

 

[Sektor]

Yes, you "guessed" right! Actually Sheets("menu") is taken from code I wrote.
1. Code iterates thru all OLEObjects in this worksheet and takes only buttons.
2. It creates EventsTrapper class which holds WithEvents Button property. This is the connection between button and handler.
3. EventsTrapper is added to collection to hold all buttons in one place. This is for convenience.
4. Now when you click any button, the event handler Button_Click in EventsTrapper class is triggered.

You can also add controls from UserForm, but then code must be changed a bit. Here's complete code if you wanna use UserForm (assuming frmTest as name):

In ThisWorkbook module:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Workbook_Open()
    
    [COLOR="Blue"]Dim[/COLOR] ctrl [COLOR="Blue"]As[/COLOR] MSForms.Control
    [COLOR="Blue"]Set[/COLOR] col = [COLOR="Blue"]New[/COLOR] Collection
    
    ' This loop connects all buttons in UserForm
    ' to one event hanlder in EventsTrapper class.
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] ctrl [COLOR="Blue"]In[/COLOR] frmTest.Controls
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]TypeOf[/COLOR] ctrl [COLOR="Blue"]Is[/COLOR] MSForms.CommandButton [COLOR="Blue"]Then[/COLOR]
            [COLOR="Blue"]Set[/COLOR] EventTrapper = [COLOR="Blue"]New[/COLOR] EventsTrapper
            [COLOR="Blue"]Set[/COLOR] EventTrapper.Button = ctrl
            [COLOR="Blue"]Set[/COLOR] EventTrapper.Form = frmTest
            col.Add EventTrapper
            [COLOR="Blue"]Set[/COLOR] EventTrapper = [COLOR="Blue"]Nothing[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

In standard module:

[COLOR="Blue"]Public[/COLOR] col [COLOR="Blue"]As[/COLOR] Collection
[COLOR="Blue"]Public[/COLOR] EventTrapper [COLOR="Blue"]As[/COLOR] EventsTrapper

In class module:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]WithEvents[/COLOR] Button [COLOR="Blue"]As[/COLOR] MSForms.CommandButton
[COLOR="Blue"]Public[/COLOR] Form [COLOR="Blue"]As[/COLOR] MSForms.UserForm

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Button_Click()
    ' Actions here...
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Jameo]

Thanks so much for that mate, just what is the difference between using an OLEObject and a UserForm? Just if you have buttons on a sheet as oppose to on a form?

Cheers

 

[Sektor]

No difference. Some people prefer put buttons directly onto sheet and some like UserForms. Also, there's one more way to work with controls: Forms Controls.
For them there's no need to write classes and all that stuff. You add Forms button to sheet and select in context menu "Assign Macro". However, they can respond only on one event.
You can also programmmtically add Forms controls. The Sub for Forms control can be written before creating button (as opposed to ActiveX control). After creating button, you just assign OnAction property to the name of Sub. That's all.
Here's example of automatic creation Forms button.