combine update subs

[Arie Bos]

Hello excel friends,

I have 12 textboxes asking for a simple integer. there is a label following, where the values of each textbox is added up.
This is all very simple, but the code below does look a bit primitive to me to have to put the _change of each textbox in a separate subroutine. I wonder if this could not be written in a much more elegant way?

Sub calctotal()

Dim TotalCostsforInstallation As Long
TotalCostsforInstallation = _
    Val(Amount01.Value) * Val(UnitCost01.Value) + _
    Val(Amount02.Value) * Val(UnitCost02.Value) + _
    Val(Amount03.Value) * Val(UnitCost03.Value) + _
    Val(Amount04.Value) * Val(UnitCost04.Value) + _
    Val(Amount05.Value) * Val(UnitCost05.Value) + _
    Val(Amount06.Value) * Val(UnitCost06.Value) + _
    Val(Amount07.Value) * Val(UnitCost07.Value) + _
    Val(Amount08.Value) * Val(UnitCost08.Value) + _
    Val(Amount09.Value) * Val(UnitCost09.Value) + _
    Val(Amount10.Value) * Val(UnitCost10.Value) + _
    Val(Amount11.Value) * Val(UnitCost11.Value) + _
    Val(Amount12.Value) * Val(UnitCost12.Value)
   
Debug.Print "TotalCostsforInstallation = " & TotalCostsforInstallation
    LblTotalEstInvestment.Caption = Format(TotalCostsforInstallation, "#,##")
End Sub


Private Sub Amount01_Change()
    calctotal
End Sub
Private Sub Amount02_Change()
    calctotal
End Sub
Private Sub Amount03_Change()
    calctotal
End Sub
Private Sub Amount04_Change()
    calctotal
End Sub
Private Sub Amount05_Change()
    calctotal
End Sub
Private Sub Amount06_Change()
    calctotal
End Sub
Private Sub Amount07_Change()
    calctotal
End Sub
Private Sub Amount08_Change()
    calctotal
End Sub
Private Sub Amount09_Change()
    calctotal
End Sub
Private Sub Amount10_Change()
    calctotal
End Sub
Private Sub Amount11_Change()
    calctotal
End Sub
Private Sub Amount12_Change()
    calctotal
End Sub
Private Sub UnitCost01_change()
    calctotal
End Sub
Private Sub UnitCost02_change()
    calctotal
End Sub
Private Sub UnitCost03_change()
    calctotal
End Sub
Private Sub UnitCost04_change()
    calctotal
End Sub
Private Sub UnitCost05_change()
    calctotal
End Sub
Private Sub UnitCost06_change()
    calctotal
End Sub
Private Sub UnitCost07_change()
    calctotal
End Sub
Private Sub UnitCost08_change()
    calctotal
End Sub
Private Sub UnitCost09_change()
    calctotal
End Sub
Private Sub UnitCost10_change()
    calctotal
End Sub
Private Sub UnitCost11_change()
    calctotal
End Sub
Private Sub UnitCost12_change()
    calctotal
End Sub

 

[johnnyL]

Can you post the code that is calling for all of those subroutines?

 

[Arie Bos]

Hi JohnnyL,
amount01-12 and Unitcost01-12 are all textboxes.
In 'Sub Calctotal', variable TotalCostsforInstallation must be updated when the user fills in a number in these textboxes. So the above is the total code to display the sum of all textboxes in label LblTotalEstInvestment.

 

[GWteB]

What you're asking for is quite doable. First of all, I would like to recommend that you take a closer look at your calculation. Apparently you work with amounts of money and your locale uses the comma as the decimal delimiter. Since you use a Long type for the outcome, I have my reservations about the latter. Perhaps using the Currency type is worth considering.

Regarding your query, a more elegant way could be to use a worksheet for your calculations, this could even be a hidden worksheet. This can be done in muliple ways:

1. You may set up a worksheet in advance in which you name a cell per textbox which corresponds to the name of your textbox. The calculation can take place on your worksheet, and the cell with the total appears in the label on your userform on a text box change.
2. You may add a new (hidden) worksheet during the initialization of the userform and "link" the text boxes to individual cells on that worksheet during run-time.
3. Stick with VBA for the calculations.

Regardless of which method is used, the key to solving your "problem" is to introduce your own event handler for the text boxes. By instantiating such an event handler each text box will have its own handler in memory, but they are sharing the same code.

Note that the code below uses method 1) so there have to be some Named Ranges in your workbook, all with names of the successive text boxes.
Code is in its simplest form but may give you an idea of the possibilities.


This goes in a Class module to be renamed TxtBoxEvents:

Option Explicit

Public WithEvents TextBoxGroup As MSForms.TextBox

Private Type TLocals
    Usf As Object
    Sht As Worksheet
End Type
Private this As TLocals

Public Sub Init(ByVal argHostUsf As Object, ByVal argSheet As Worksheet)
    Set this.Usf = argHostUsf
    Set this.Sht = argSheet
End Sub

Private Sub TextBoxGroup_Change()
    ' method 1) as per MrExcel post: usage of a Named Range which name equals the name of the TextBox
    If this.Sht.Parent.Name = ActiveWorkbook.Name Then
        On Error Resume Next
        Range(TextBoxGroup.Name).Value = TextBoxGroup.Value
    Else
        ' some other workbook is active
        ' therefore the expected Named Ranges will be out of scope > do nothing
    End If
End Sub

This goes in the Userform's code-behind module:

Option Explicit

Private Type TLocals
    AllTextBoxes()  As New TxtBoxEvents
End Type
Private this As TLocals

Private Sub UserForm_Initialize()
    CreateEventHandlers
End Sub

Private Sub CreateEventHandlers()
    Dim ctrl As MSForms.Control, ControlCount As Long
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            ControlCount = ControlCount + 1
            ReDim Preserve this.AllTextBoxes(1 To ControlCount)
            With this.AllTextBoxes(ControlCount)
                Set .TextBoxGroup = ctrl
                .Init argHostUsf:=Me, argSheet:=ThisWorkbook.Worksheets("Sheet1")
            End With
        End If
    Next ctrl
End Sub

 

[Arie Bos]

Wow.. GWteB, this is quite something... If I knew you spent so much work I would not have dared to ask... Thank you so much for the explanation.
As for the calculation, I am located in the Netherlands, but the program will be used by my friend in the US. After reading your suggestion, I simply used LblTotalEstInvestment.Caption = FormatCurrency(TotalCostsforInstallation). The result is a number shown in Euros. I guess in the US it will pick up the local setting and show a $ sign?

To be honest, your code is 10 levels above my skills (therefore I only could make the non-elegant code haha).
I understand the concept of what you write in solutions 1,2,3. Will work on this over the weekend.

many thanks again!
regsrds,
Arie

 

[GWteB]

You are welcome.

I understand the concept of what you write in solutions 1,2,3.

Those are just a few of many. For example, you could fill the Tag property of each text box with an address of a cell, such as $A$1, each a different one obviously. You then no longer have to create Named Ranges. Just amend the code in a way not the Name property of the text box is used, but its Tag property.

As noted, there's more than one way to skin a cat, just use what you feel most comfortable with. Sticking to your VBA calculation would require the following modification of the post #4 event handler, provided the "calctotal" procedure has Public scope (which is default if the Sub statement is not preceded with the Private keyword):

Private Sub TextBoxGroup_Change()
    ' invoke the calctotal method on the host userform
    this.Usf.calctotal
    
'''    ' method 1) as per MrExcel post: usage of a Named Range which name equals the name of the TextBox
'''    If this.Sht.Parent.Name = ActiveWorkbook.Name Then
'''        On Error Resume Next
'''        Range(TextBoxGroup.Name).Value = TextBoxGroup.Value
'''    Else
'''        ' some other workbook is active
'''        ' therefore the expected Named Ranges will be out of scope > do nothing
'''    End If
End Sub

If I knew you spent so much work ...

I didn't, the code was just sitting there .... This construct I use in almost all user forms, most of which have dynamically (during run-time) added controls, so a custom event handler is the only way to be able to act on user input.