0

I have a userform with 120 textboxes (10 years x 12 months). Going across are the months, and at the end is the total. This is repeated 10 times for the 10 years going downwards

I would like it so the total label is updated when the values for the corresponding years' months change.

Textboxes are labelled for each year and month. The first one is 'TextBox1_1', the second is 'TextBox1_2'. for the third year, and fifth month it is 'TextBox3_5' and so forth.

I have a module which is set up to calculate the totals:

Sub totalVal(i As Integer)
  Dim arr(1 To 12) As Integer
  Dim j As Integer

  For j = 1 To 12
    If Trim(Basic_Info_Setup.Controls("TextBox" & i & "_" & j).Value & vbNullString) = vbNullString Then
    Else
      arr(j) = Basic_Info_Setup.Controls("TextBox" & i & "_" & j).Value
    End If
  Next j

  Basic_Info_Setup.Year1_Total.Caption = Application.WorksheetFunction.Sum(arr)
End Sub

The 'i' will be passed as the year and the rest can be calculated. However to use this would a change event in all 120 textboxes which is far from ideal, but not undoable.

I have read and understand this is possible with a class module. This one which shows how to do it with textboxes (Here) and I also had a look at how this one (Here) albeit it confused me more than it helped.

What do I need to do to make this class module for me?

I currently have in the class module, which is called 'clsLabel'

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set Control(tb As MSForms.TextBox)
    Set MyTextBox = tb
End Property

Private Sub MyTextBox_Change()
    totalVal (1)
End Sub

and in the userform

Private Sub UserForm_Initialize()
Dim tbCollection As Collection
    Dim ctrl As MSForms.Control
    Dim obj As clsLabel

    Set tbCollection = New Collection
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is MSForms.TextBoxThen
                Set obj = New clsLabel
                Set obj.Control = ctrl
                tbCollection.Add obj
            End If
        Next ctrl
    Set obj = Nothing
  End Sub

when things work I would be happy to change the passing 'i' integer to be dynamic, I just have it set at 1 for now to see if it works for a specific year

There are no errors. EDIT: corrections to above code as it now works

thanks

Tejkaran Samra
  • 96
  • 1
  • 14
  • 1
    Your collection `tbCollection`is local to the `Userform_Initialize` Sub, and is consequently destroyed at the end on the sub. Try to put the `Dim tbCollection as Collection` outside the Sub, as a form global variable. – Vincent G Jul 24 '18 at 07:14
  • Currently only your total labels go in the collection, but there is no "Change" event for labels. you need to make a subclass for the months textboxes, not for the total labels. – Vincent G Jul 24 '18 at 07:23
  • It works, sorry for deleting my comment. I needed to change the labels to textbox and it worked fine (as well as making it a global variable). I am keen to know how to use classes so I will be deconstructing this to understand how it works. Many thanks. – Tejkaran Samra Jul 24 '18 at 07:25

0 Answers0