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

    Code
    1. Option Explicit Public WithEvents TBox As MSForms.TextBox
    2. Private Sub Tbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    3. KeyAscii = CheckInput(KeyAscii)
    4. End Sub
    5. Public Function CheckInput(ByVal KeyAscii As MSForms.ReturnInteger)
    6. CheckInput = IIf((KeyAscii >= 48) And (KeyAscii <= 57), KeyAscii, vbKeyClear)
    7. 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...



    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


    Hi,


    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 "Like" icon ...just 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

    Code
    1. Public WithEvents myBox As MSForms.TextBox
    2. Event DEntered()
    3. Property Get HasD()
    4. HasD = myBox.Text Like "*D"
    5. End Property
    6. Private Sub myBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    7. If Not (Me.HasD) And Chr(KeyAscii) = "D" Then RaiseEvent DEntered
    8. End Sub


    in userform

    Code
    1. Public WithEvents CustomTextBox As clsCustomtextbox
    2. Private Sub CustomTextBox_DEntered()
    3. MsgBox "D was pressed"
    4. End Sub


    and has to have textbox1

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


    Class clsFrmCtls

    Code
    1. Option Explicit
    2. Public mName
    3. Public mFrm As Object
    4. Public WithEvents mCommandbutton As MSForms.CommandButton
    5. Private Sub mCommandButton_Click()
    6. Call mFrm.SelectedChange(mName)
    7. End Sub


    or

    Code
    1. Option Explicit
    2. Public mName
    3. Public mFrm As Object
    4. Public Event SelectedChange(objCtr)
    5. Public WithEvents mCommandbutton As MSForms.CommandButton
    6. Private Sub mCommandButton_Click()
    7. RaiseEvent mFrm.SelectedChange(mName)
    8. End Sub


    userform

  • 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 http://www.ozgrid.com/forum/sh…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: Your Favourite VBA Class Module Example


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


    http://www.cpearson.com/excel/classes.aspx


    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

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


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


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • 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


    Code
    1. Option Explicit
    2. Private Sub Worksheet_Activate()
    3. Call Hook_TextBoxes
    4. End Sub
    5. Sub Worksheet_Deactivate()
    6. Call UnHook_TextBoxes
    7. End Sub


    class module named clsTexBox

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