Class Programming: Objects on Userforms

Re: Class Programming: Objects on Userforms

Quote

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

' Make sure the Option Compare is not duplicated
Option Explicit
Option Compare Text


Public WithEvents txt As MSForms.TextBox


Private Sub txt_Change()
   '// Standard and Bad backcolours
   Const STD As Long = &H80000005
   Const BAD As Long = &HC0C0FF
   
   '// The text can be resolved to a date?
   '// Assumptions are mode for partial dates entered,
   '// 23/5 will be assumed to be current year
   If IsDate(txt.Text) Then
      
      '// does the date appear in the Holdiay list?
      If Holiday(CDate(txt.Text)) Then
         txt.BackColor = BAD
      Else
         txt.BackColor = STD
      End If
   Else
      txt.BackColor = BAD
   End If
   


End Sub


Private Function Holiday(d As Date) As Boolean
   
   Dim r As Excel.Range
   
   '// It will err is certain circumstances, especially if the year component is 3 digits long
   '// No problem, it will be corrected next keystroke.
   On Error Resume Next
   
   Set r = Sheets("Holidays").Range("A:A").Find(What:=d)
   Holiday = Not r Is Nothing
   ' Alternative
   'WorksheetFunction.CountIf(Sheets("Holidays").Range("A:A"), d) > 0
   
End Function

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

Private Sub UserForm_Initialize()


   Dim c As Control
   Dim cl As clsTXT
   
   Set coll = New Collection
   
   For Each c In Me.Controls
   
      If TypeName(c) = "TextBox" And InStr(c.Tag, "Date") > 0 Then
            
         Set cl = New clsTXT
         Set cl.txt = c
         coll.Add cl
      End If
   Next
   
End Sub


Private Sub UserForm_Terminate()


   Set coll = Nothing
   
End Sub

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...