VBA Classes

J

Jameo

Active Member
Joined
Apr 14, 2011
Messages
270
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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:

Code: 
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.
 
Upvote 0
Here's example of using classes. Ask if you don't understand something.
 
Upvote 0
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
 
Upvote 0
In ThisWorkbook module.
Code: 
[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:
Code: 
[COLOR="Blue"]Public[/COLOR] col [COLOR="Blue"]As[/COLOR] Collection
[COLOR="Blue"]Public[/COLOR] EventTrapper [COLOR="Blue"]As[/COLOR] EventsTrapper

In class module:
Code: 
[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]
 
Upvote 0
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"

Rich (BB code): 
Public col As Collection
Public EventTrapper As EventsTrapper
Simply defines the collection and event trapper name

Finally,

Rich (BB code): 
Option Explicit
 
Public WithEvents Button As MSForms.CommandButton

Defines the "Button" Event
Rich (BB code): 
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.
 
Upvote 0
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:
Code: 
[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:
Code: 
[COLOR="Blue"]Public[/COLOR] col [COLOR="Blue"]As[/COLOR] Collection
[COLOR="Blue"]Public[/COLOR] EventTrapper [COLOR="Blue"]As[/COLOR] EventsTrapper

In class module:
Code: 
[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]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
138
NKUNZI
N
Q
  • Solved
VBA: Dynamically Create Active X Frame and Yes / No Radio Button
2
Replies
13
Views
246
Domenic
D
T
  • Question Question
Multiple filters on filtered list
Replies
1
Views
611
Cubist
Cubist
D
  • Question Question
VBA Basic Userform - add -update -delete
Replies
3
Views
679
Drexi
D
M
  • Question Question
How To Allow Only One Checkbox to Be Checked in Excel per Table
Replies
1
Views
622
bobsan42
bobsan42

Forum statistics

Threads
1,224,521
Messages
6,179,289
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top