Class Programming: Objects on Userforms

  • Dear all

    I use a textbox called date on a number of userforms. The textbox checks on exit whether the date is a holiday.. with some additional action.

    I am an absolute beginner with class programming but it is my understanding that you can create a new instance of a class, with all the properties associated with the class.

    Is it possible to create a textbox class called txtDate which checks for errors when the user exits the textbox?
    I assume that the exit function would also need to be a class?

    This would enable to me to reduce the code needed in my project .. most likely by 20% as I could then apply the same technique to other controls.

    Isn't this the idea of class programming?

    I have attached an Excel file with many userforms .. each with a textbox called txtDate
    The function checks whether a date is a holiday.

    I do sincerely hope that someone can help me with this.


  • Re: Class Programming: Objects on Userforms


    Is it possible to create a textbox class called txtDate which checks for errors when the user exits the textbox

    Yes... and No.

    You can create a class and declare a variable of type TextBox (or any other control) using 'WithEvents'. This causes the events raised in the textbox to be mirrored in the class.

    The 'No' bit is that VBA, for whatever reason, does not 'expose' the Exit and AfterUpdate events in a class. There are ways around that, but complicated...

    Start simply with a class to check if a valid date, and not a holiday, when a textbox is changed.

    for this, you need to insert a new class module. Name it 'clsTXT' (Press 'F4' when viewing the code for the class and use the Properties list)

    Add all the following code to the class

    That's the class...

    Take frmDate as an example, add

    '// Used to hold the instances of the TextBox classes
    Dim coll As Collection

    to the General Declarations section at the very top. The collection will be used to hold instances of the class defined in the Initialise event - otherwise it goes out of scope when the Initialise event completes.

    2 more procedures

    The Terminate event is just to tidy up and destroy the collection ands the objects it contains. The Initialise event is fairly straightforward and easy to understand. Just one thing to note, Add the text 'DATE' to the Tag property of any textbox that will contain a date. Only those textboxes will be processed.

    Side note. Use 'Option Compare Text' to make text comparisons case insenitive - saves having to upper/lower case text when comparing.

    It may look like a lot of work, but you only have to declare the collection, copy the code in the initialise event and add the 'Date' flag to the Tag property to use this in another userform.

    OK, I know it's not pretty when the textboxes go red while the user is inputting, but it's a start...

  • Re: Class Programming: Objects on Userforms

    Hello cytop

    Thank you so much for your code.
    I have adapted it and I have one more question.

    The code assumes that a partial date is always in the current year.
    Is there a way to change this? To make it check only once a full date has been entered?

Participate now!

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