Class approach for new controls questions

[Jomathr]

Good morning VBAx community,

I've been working on a billing personnal project to explore VBA in depth and would like your opinion on how to approach certain facet of it. I am not here to get the whole code for doing this, more about the concepts of how to build it using classes.

Here is what I basically want to do:

CBProduct1 TxtQty1 TxtStartDate1 TxtEndDate1 TxtRate1 TxtSubTotal1

Each of those represent a control (text boxes except for product wich is a combobox) wich are contained in a frame on a userform
Now I want to have a class (ClsNewBillLine) to create a new set of these controls if Product <> vbnull
I intend using the controls.add method to achieve this, just not quite sure how to check the condition for the last Product combobox yet

Aside from the properties wich will be common for each of those I need to be able to dynamically add some controls to a collection of controls for validation for example:
All the dates textbox should be validate by a Class to check if the date format is valid and that end date is after start date

Product1 and Rate1 are linked and will be populated with a DBfetch Class (Access 2010 as a test DB)

And last but not least I need to be able to copy all the entered value to an excel sheet, this part I am not sure I need a class, but if there is a more efficient way then using a for loop looping trough the controls I am open to it.

I am not very experienced with Classes as a whole and I'm sure there are limitations in VBA compared to VB regarding them. The part that I can't figure out is mostly how to assign event handlers to the controls generated by the class:

How do I assign the event handler ClsNewBillLine to the CB Product2 if Product2 <> vbnull within that same class.
I intend of using the name of the ProductX combobox to get the current number +1 for naming the new set of controls using Control.name.Right(1)

Also do the class in vba respect the traditionnal Contructor/Method/Properties?
I am quite familiar with VBA althought some subtilities still escape my knowledge

I will keep working on this and add an attachment as the project get clearer

Any help/opinions will help and as always thank you for your time!

[Jomathr]

I've made a Form to illustrae what I am trying to accomplish in my previous post

No working code in it, just some code for DB connection in the Form initialise as comments. If need arise further down the path I'll join the sample DB as well but I doubt it will be necessary

Invoice with class.xlsm

The part I want to automate using Class is everything in the detail Frame

[mikerickson]

You might want to look at this
http://www.mrexcel.com/forum/excel-q...g-runtime.html

[Jomathr]

Mr. Mikerickston that is brilliant! It will sure help me start up on classes!

I'll post another version when I get the basic mechanic of it to work for the benefit of everyone on this forum who are beginners with classes

Thank you very much!

[mikerickson]

The trick to using clsRTControls is that each functional type of control should have its own custom class.
In your case, there should be 6 variables of type clsRTControls, that way the events can be tailored for that class. You should also give the added controls similar names so that you can find them later. This made up code, which is missing a lot of the set up, shows one way to retrieve different controls from different custom objects.

Public WithEvents rtProductComboBoxes as clsRTControls
Public WithEvents rtTxtQty as clsRTControls
Public WithEvents rtTxtStartDate as clsRTControls
Public WithEvents rtTxtEndDate as clsRTControls
Public WithEvents rtTxtRate as clsRTControls
Public WithEvents rtSubTotal as clsRTControls

Sub AddOneBatch()
    Dim Index As Long
    Index = rtProductComboBoxes.Count + 1

    With rtProductComboBoxes.Add("forms.ComboBox.1", chr(65 + index) & "Product")
        ' set .Top, .Left and other properties
    End With

    With rtTxtQty.Add("forms.TextBox.1", Chr(65+index) & "Qty")
        ' set properties
    End With

'...

    With rtTxtSubTotals.Add("forms.TextBox.1", Chr(65 + index) & "SubTotal")
        ' set properties
    End With
End Sub()

Sub CalculateRow(Index as Long)
    Dim Prefix as String
    Prefix = Cstr(65+index)
    rtTxtSubTotal.Item(Prefix & "SubTotal").Text = Val(rtTxtQty.Item(Prefix & "Qty").Text) * Val(rtTxtRate.Item(Prefix & "Rate").Text
End Sub

Another problem, is the clsRTControls and clsRTControl do not (yet) handle ComboBoxes. Its just a matter of adding a Public WithEvents variable to clsControl of type forms.ComboBox and adding all the Event codes for ComboBox and adding the Events to pass upward.

clsRTControls is a work in progress. If you need me to adjust the classes to accept comboboxes, PM me and I'll get onto it. ALSO, if you PM me, be prepared with the Events that you want to be able to capture. I should also warn you that the Exit and Enter events are quirky and can't be trusted. I would no use them (or the non-existent After or Before Update) for data Validation.

The idea is that, when finished, clsRTControls and clsRTControl can be added to a project and no further coding be done in those modules. All the programming would be done in the module of the userform to which the controls are added.

[snb]

You might even have a look at:

http://www.snb-vba.eu/VBA_Userform_i...ntrole_en.html

[Paul_Hossler]

Chip is always a good read

http://www.cpearson.com/Excel/Classes.aspx


Paul

[Jomathr]

I've been looking a bit more in depth in the code you posted Mikerickson, I understand the general idea and concept.

There is one part that caught my eye and not sure about it, Everycontrol you create use "chr(65+index)& name" so you are using the Alphabet as a prefix to the control name and allow for 26 combobox then the next one you get [ as a prefix wich I'm sure will end up causing problems. Is there any advantage in using this naming convention over "name & index"?

I appreciate the offer on the pm, but this is a learning project, wouldn't be much learning involved if I have it done by someone else. I'll play with it and let you know if I get stuck.

Thank you for your reply snb

I checked the approach you are taking and it is well explained and for a project that all the controls of a type must have the same behavior it would be awesome. But in this case where I have multiple Textbox with different behavior if I tweak the code I would end up with something similar to what mikerickson posted. I did get some insight on the mechanics of classes in VBA. Also I love the "Typename()" and can see many use with it that I strugle with in the past!

There is also the array part that I find interesting, and I was asking myself if a collection of controls is in a way the same as an array of controls, since with both I could, for example add all the values in it?

I appreciate the time both of you are taking! Thank you

[snb]

You can choose a collection, an array or a dictionary to store instances of classes in: but don't mix them up.

[Jomathr]

snb, Is there an advantage to use one over another or is it a questions of preferences like other things in VBA?

[Jomathr]

Mikerickson:

You mentionned in your previous post that I would have to adjust clsRTControls and clsRTControl for ComboBox. After looking at the code in the class I suppose I have to add the following to the classes:

clsRTControl:

'Control properties

Public WithEvents RTComboBox As MSForms.ComboBox
'...
Rem release variables
Set RTComboBox = Nothing
...
Rem events triggered by controls


'ComboBox section
Private Sub RTComboBox_Change()
    Set RTParent.EventedRTControl = Me
    RaiseEvent Change(RTControl)
End Sub
Private Sub RTComboBox_Click()
    Set RTParent.EventedRTControl = Me
    RaiseEvent Click(RTControl)
End Sub
Private Sub RTComboBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Set RTParent.EventedRTControl = Me
    RaiseEvent DblClick(RTControl, Cancel)
End Sub
Private Sub RTComboBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Set RTParent.EventedRTControl = Me
    RaiseEvent KeyDown(RTControl, KeyCode, Shift)
End Sub
Private Sub RTComboBox_KeyPress(ByVal keyAscii As MSForms.ReturnInteger)
    Set RTParent.EventedRTControl = Me
    RaiseEvent KeyPress(RTControl, keyAscii)
End Sub
Private Sub RTComboBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    Set RTParent.EventedRTControl = Me
    RaiseEvent MouseDown(RTControl, Button, Shift, x, y)
End Sub
Private Sub RTComboBox_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    Set RTParent.EventedRTControl = Me
    RaiseEvent MouseMove(RTControl, Button, Shift, x, y)
End Sub
Private Sub RTComboBox_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    Set RTParent.EventedRTControl = Me
    RaiseEvent MouseUp(RTControl, Button, Shift, x, y)
End Sub

And for ClsRTControls add the case ComboBox in Select Case statement for each event and function:

Function Add(progID As Variant, Optional Name As String = "", Optional isVisible As Boolean = True) As MSForms.Control
    Dim newControl As MSForms.Control
    Dim newRTControl As ClsRTControl


    If TypeName(progID) = "String" Then
        If Name = vbNullString Then
            Set newControl = Parent.Controls.Add(progID, Visible:=isVisible)
        Else
            Set newControl = Parent.Controls.Add(bstrProgid:=progID, Name:=Name, Visible:=isVisible)
        End If
    Else
        Set newControl = progID
    End If
    
    If Not (newControl Is Nothing) Then
        Set newRTControl = New ClsRTControl
        With newRTControl
            Set .RTParent = Me
            Set .RTControl = newControl
            Select Case TypeName(.RTControl)
                Case "Label"
                    Set .RTLabel = newControl
                Case "ComboBox"
                    Set .RTComboBox = newControl
                Case "ListBox"
                    Set .RTListBox = newControl
                Case "TextBox"
                    Set .RTTextBox = newControl
                Case Else
                    Set newRTControl = Nothing
            End Select
        End With

'...

As for the code you posted in your post, I know it goes in the userform but it is still vague in my mind at what point the subs you added are called, for example the addOneBatch() should be triggered when the product ComboBox <> vbnullstring on the last added ComboBox only. Tried to check where this should be called and I think it would be called From ClsRTControls in the add function.

Once again thank you for your time

[mikerickson]

There is one part that caught my eye and not sure about it, Everycontrol you create use "chr(65+index)& name" so you are using the Alphabet as a prefix to the control name and allow for 26 combobox then the next one you get [ as a prefix wich I'm sure will end up causing problems. Is there any advantage in using this naming convention over "name & index"?

The idea is "AProduct" is linked to "AQty", "ARate" etc.
When I am just given a control (someCtrl) and I want to get the quantity that is associated with it, I can use code like

 MsgBox Userform1.Controls(Left(someCtl.Name,1) & "Qty").Value

The only advantage that I see is that Left(someCtrl.Name, 1) is easier (for me) than extracting a numeral at the end of a name (e.g. "Qty13"). If you are going to have more than 26 versions, it would be better (IMO) to use "1Qty", "2Qyt",..."10Qty" and then extact with

MsgBox Userform1.Controls(Val(someCtl.Name,1) & "Qty").Value

[Jomathr]

I must be missing something, I inserted the missing parts for ComboBox in both ClsRTControl and ClsRTControls (I think, still trying to digest the thing as a whole)

From the combobox that is already present on the userform I try to call the addOneBatch and I get an error Object variable or with bloc variable not set on the index variable line
I doubled checked for any typos and couldn't find any. I know there aren't any RTComboBox on the form at the moment but shouldn't it return 1 since the it's .count +1?

I included the version with the added classes and some more code I made to change my mind while trying to figure out where it goes wrong

Invoice with class2.xlsm

Just to be clear, I am not in a hurry and doing this to learn a bit more about VB so no pressure on anyone. I do appreciate how fast you guys reply thought!
Also if you see something that is not good practice and not marked to be removed after testing let me know

Thank you

[mikerickson]

In frmFacturClass, you have this declaration line.

Public WithEvents RTComboBox As clsRTControls

(Note, RTComboBox is also a Public Property of your clsRTControl. Excel won't confuse the two if you don't.)
Before you can add any control to that member of clsRTControls, you have to instansize the object and you have to set its .Parent property. I would put these two lines at the beginning of the UserForm_Initialize event.

Set RTComboBox = New clsRTControls
Set RTComboBox.Parent = Me

I should also mention that this has not been tested with controls put in frames. If you want all the controls from RTComboBox in a frame, the .Parent should be set to Me.Frame1 rather than Me. But, these classes haven't been tested for that and I'm not sure it will work right.

[mikerickson]

Just did some checking. Setting the .Parent of an clsRTControls to a Frame or MultiPage should work fine.

[Jomathr]

I just tried it as well in a frame and it works just fine. but on the second ComboBox (The newly created one) when Change event is triggered it ends up being detected as TextBox by the TypeName() Function.

I will investigate it a bit later, just tried it in a hurry

thank you!

[Jomathr]

It turns out it is not detected as a TextBox, Since I am always using the Option Explicit it was detecting an error on that line since Trigger was not declared and being the first line where it appear it made me think it was detected as a textBox.

I got it to work with the comboBoxes althought I had to tweak the AddOneBatch to get Product & index to have a number append at the end of my comboBox for validation. In this case I needed to check if it's the last created comboBox that triggered the event. Since it's adding a line everytime I changed from "" to any value I didn't want to add line if the first combobox is set back to empty then changed again to a value.

Here is what I used:

Private Sub RTComboBox_Change(ctrl As MSForms.Control)
If ctrl.Value <> "" And Mid(ctrl.Name, 8) = RTComboBox.Count Then
    AddOneBatch
End If
End Sub

Since I know all my ComboBox will start with the word "product" it's easy to get the number that is following.

I'll get the other controls to be added then post another file so everyone can follow.

The more I look at those classes you made, the more awesome they look!

Thank you!

[Jomathr]

Good Morning gentlemens,

Things are going along decently despite the little time I have atm for the uptdate of the file and I've been re-reading the post and I have a question for Mikerickson:

Mikerickson: In your second post you mention that before and after updates are none existent for now, is it because it can't be done or just that it isn't implemented yet?
If it isn't implemented I'll try my hand at it to help you on that work in progress.

Thank you

[mikerickson]

I have implimented soExit and soEnter events for clsRTControls. ("sort of Enter" "sort of Exit")

They are based on MouseDown and KeyDown events and trap when the mouse or Tab/Return key moves the control to or from a control in a clsRTControls collection object.
(more details about soExit and soEnter here,
http://www.mrexcel.com/forum/excel-q...g-runtime.html)

Before and After Update involve detecting a change in the control's value, there is no soAfterUpdate event in progress.

I'm currently fleshing out clsRTControls to handle more controls (not RefEdit, Frame or Multipage) and more events.
(I've learned that the BeforeDragAndDrop event of a TabStrip has paramaters that aren't present in other controls. Furthermore, when a TabStrip has the focus, pressing Tab will advance focus to the next control, but pressing Return will not.)

When I finish this step, I will be posting the code back at Mr. Excel (where this first appeared) and post a link here. Perhaps by the end of the weekend.

If you are using Before or AfterUpdate to validate a control's entry (e.g. did the user enter a number), there are usually workarounds that can avoid those events. If formatting is the issue (e.g. display only two decimal places), soEnter and soExit should work, within their limitations.

[Jomathr]

Good Morning Gentlemans,

I've been working with the SoExit over the weekend and one thing does come out when using it for validation, it runs twice since you call it from the userform, make the validation the goes on to the tab index checking section and pass all the controls. After that point SoExit is triggered again by EventedRTControl_KeyDown (tab key). Now I've been trying to work around this one without changing the code in the ClsRTControls class with the Eventdisabled you put there without much success.

Now I could do the validation at the end when the user click the "finish" button but I'm sure a case will arise where it has to be done on exit, might as well try to solve it now

Code is a bit messy since it took me a while to figure out why it was running twice, at first I thought it was the .setfocus that caused it:

Public Function DateValidation(ByVal CtrlObject As MSForms.Control)


Dim D As Date
On Error GoTo errhandler
D = CtrlObject.Value




If Not (D Like "####/##/##") Or Not IsDate(D) Then '= Format(D, "YYYY/MM/DD")
    MsgBox "La date que vous avez saisie est invalide, veuillez essayer de nouveau", vbCritical, "Erreur de saisie"
    CtrlObject.SetFocus
    CtrlObject.Value = ""
    CtrlObject.BackColor = vbRed
    EventsDisabled = True
    Exit Function
ElseIf Not CtrlObject.Value = "" Then
    CtrlObject.BackColor = vbWhite
    DateValidation = False
    Exit Function
End If


errhandler:
CtrlObject.SetFocus
MsgBox "Erreur de type: La date que vous avez saisie est invalide, veuillez essayer de nouveau", vbCritical, "Erreur de saisie"
CtrlObject.Value = ""
CtrlObject.BackColor = vbRed
EventsDisabled = True
DateValidation = False


End Function

and in the Userform I have:

Private Sub RTTxtStartDate_soExit(ctrl As MSForms.Control)


If Not DateValidation(ctrl) Then Exit Sub


End Sub

Hope there is a way around this that you already know of!