Your Favourite API or VBA Class Module Example

  • What is your Favourite Excel VBA Class that you frequently use
    CTextbox class is used to only allow input of numbers in userform text boxes.. Very useful

    Class Module name CTextbox

    Option Explicit Public WithEvents TBox As MSForms.TextBox
    Private Sub Tbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
        KeyAscii = CheckInput(KeyAscii)
    End Sub
    Public Function CheckInput(ByVal KeyAscii As MSForms.ReturnInteger)
        CheckInput = IIf((KeyAscii >= 48) And (KeyAscii <= 57), KeyAscii, vbKeyClear)
    End Function

    Userform code

    Please add your favourite and most frequently use class

  • Trigger a control change event in Userform

    another Class written by jindon to trigger a control change event for comboboxes in a userform and linking a result to a textbox

    Class module name Class1

    userform code

    Im sure everyone has a usefully object they have created or use that can be added

  • Popup menu at mouse position

    Generates a Popup menu at the mouse position - or any position you like. I've used this to build a menu 'bar' on a userform...

    [Blocked Image:]

    If I could credit any original authors, I would, but SourceSafe (Yeah, I still use that!) shows the first version labelled as 'Visual Basic 4 (16 Bit)'! - that dates it a little and it has been extended/modified since - upgraded to 32 bit, then 64 bit. Wondering if I'll even care about upgrading it to 128 bit :)

    Test procedure:

    Class Module code (clsPopup)

  • Class for Linked lists


    Excellent idea to recap favourite Classes ...:smile:

    Chip Pearson has created a long time ago ...a very handy class for Linked lists ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Class Module for Database Connection

    Database connection

    Class module

  • Re: Your Favourite VBA Class Module Example

    Old example I saved from mikerickerson

    clsCustomtextbox class module

    Public WithEvents myBox As MSForms.TextBox
    Event DEntered()
    Property Get HasD()
          HasD = myBox.Text Like "*D"
    End Property
    Private Sub myBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
          If Not (Me.HasD) And Chr(KeyAscii) = "D" Then RaiseEvent DEntered
    End Sub

    in userform

    Public WithEvents CustomTextBox As clsCustomtextbox
    Private Sub CustomTextBox_DEntered()
         MsgBox "D was pressed"
    End Sub

    and has to have textbox1

    Private Sub UserForm_Initialize()
          Set CustomTextBox = New clsCustomtextbox
          Set CustomTextBox.myBox = textbox1
    end sub
  • Userforms Selected Commandbutton Object

    Class clsFrmCtls

    Option Explicit
    Public mName
    Public mFrm As Object
    Public WithEvents mCommandbutton As MSForms.CommandButton
    Private Sub mCommandButton_Click()
         Call mFrm.SelectedChange(mName)
    End Sub


    Option Explicit
    Public mName
    Public mFrm As Object
    Public Event SelectedChange(objCtr)
    Public WithEvents mCommandbutton As MSForms.CommandButton
    Private Sub mCommandButton_Click()
        RaiseEvent mFrm.SelectedChange(mName)
    End Sub


  • Resizable userform VBA Class Module Example

    This example is just brilliant ,,, a resizable userform in real time .its now my numero uno fav

    workbook at…96725&p=756114#post756114


    Original idea by Stephen Bullen years ago - it's just something I've chopped/changed and generally mutilated over the years.

    A fuller version also includes Transparency, Max/Min form sizes (without the flashing borders when resized in the Resize event (but may flicker in the client area), lock the userform in position - I'll tidy up a sample workbook and upload a little later.

  • Re: Your Favourite VBA Class Module Example

    That sample...

    There is code to limit the Max/Min size of the userform. This uses the Windows API - read the various comments marked as 'ESSENTIAL' otherwise say goodbye to your workbook... or at least that version in memory.

    2nd attachment for Pike - resize a fixed border userform when the zoom factor is changed.

  • Chart Hooks Mouse Pointer to scroll

    This is very cool
    In the example workbook attached just click in the chart to enable the chart manipulation
    Class module below for the hook and there are two auxiliary modules in the workbook

  • Re: Resizable userform VBA Class Module Example

    I like this but for us dummy's, what exactly is it and how can we put these codes to use for us in the future?

  • Re: Your Favourite VBA Class Module Example

    Thanks for all these codes. I can see a lot of work went into writing these codes. It is very kind of you to post them for others like myself who are trying to learn.

  • Re: Your Favourite VBA Class Module Example

    Class modules allow you to create your own objects in your application which can have their own properties and methods like any other object

    The one point of creating classes is so that you can encapsulate your code in one place (error handling, updating of data). Classes can help if you develop an application for others . The user doesn't need to worry about how the object works, they only need to understand how to use it's methods and properties (unless they also created the class).

    hope that helps

  • userform class RaiseEvent OnEnter control

    userform class event code from Jaafar Tribak RaiseEvent on enter or exit of control.
    This is especially useful when using multiple cascading combobox list which are dependant on each others selection
    combobox's don't have this feature

    Userform code

    Class Module named CtlExitCls

  • Update ~ Class Userform Controls Raise Events

    Programmatically enhanced to handle the errors in previous example ..

    Userform1 is now showModal=False and additional MousedownOnForm code added

    Userform1 Code

    Class Module named CtlExitCls

  • Re: Your Favourite VBA Class Module Example

    Thanks Pike... I needed this about 12 months ago :) :) good to know (now) how it can be done

    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • VBA event Class for worksheet Activex or Form Controls

    in the example workbook click any ActiveX textbox to transfer its value to range A1

    module code

    worksheet module

    Option Explicit
    Private Sub Worksheet_Activate()
        Call Hook_TextBoxes
    End Sub
    Sub Worksheet_Deactivate()
        Call UnHook_TextBoxes
    End Sub

    class module named clsTexBox

    Option Explicit
    Public WithEvents objTextBox As msforms.TextBox
    Private Sub objTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        Range("A1").Value = objTextBox.Value
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!