Apologies in advance for the long post. I think this is a relatively easy question for you more experienced VBA folks out there. I've been teaching myself VBA over the last few weeks and have learned a quite a bit from this forum and others. Thank You!!
I need some help taking the next step. I created a financial planning userform with so many controls (1200 textboxes and comboboxes across 10 multi tabs) that I eventually got the "out of memory" error. So I've now broken my giant userform into 10 smaller userforms with the corresponding subs moved into the appropriate forms. These subs calculate income vs savings vs expenses vs investing etc.
All my control events point to a specific sub based on what category it belongs to, e.g. income controls point to incomecalc(), etc. There's a LOT of duplication and it creates lots of clutter (which I hate scrolling through). With so many controls I started looking for a solution that allowed all the control events that point to a unique sub to be handled by a single master event handler, e.g. 1200 events collapsed into 10 "master" event handlers each pointing to one sub.
After much searching and reading I found a solution, i.e. creating a class (thanks J. Walkenbach whoever you are!). His simple version works. I've now started modifying it and now I'm stuck. Here's his simple solution (Handling Multiple UserForm Controls with One Event Handler - Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)).
This simple solution basically just calls the class which executes a msgbox line of code located in the class module. My need is a bit more complicated. I need my solution to a) call the class anytime a textbox or combo box is changed on my userform and b) execute a calculation sub that updates values on the userform that initiated the handling call. It is part b that has me stumped. After researching I've think the issue is that the calculation sub might need to be in a standard module (effectively creating a three modul solution of userform module + class module + standard module. I've made a few attempts at creating the "userform-->class module-->standard module-->userform" roundtrip to no avail. Help!
Here's my code.
Userform Module
Here's the class module code
I need some help taking the next step. I created a financial planning userform with so many controls (1200 textboxes and comboboxes across 10 multi tabs) that I eventually got the "out of memory" error. So I've now broken my giant userform into 10 smaller userforms with the corresponding subs moved into the appropriate forms. These subs calculate income vs savings vs expenses vs investing etc.
All my control events point to a specific sub based on what category it belongs to, e.g. income controls point to incomecalc(), etc. There's a LOT of duplication and it creates lots of clutter (which I hate scrolling through). With so many controls I started looking for a solution that allowed all the control events that point to a unique sub to be handled by a single master event handler, e.g. 1200 events collapsed into 10 "master" event handlers each pointing to one sub.
After much searching and reading I found a solution, i.e. creating a class (thanks J. Walkenbach whoever you are!). His simple version works. I've now started modifying it and now I'm stuck. Here's his simple solution (Handling Multiple UserForm Controls with One Event Handler - Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)).
This simple solution basically just calls the class which executes a msgbox line of code located in the class module. My need is a bit more complicated. I need my solution to a) call the class anytime a textbox or combo box is changed on my userform and b) execute a calculation sub that updates values on the userform that initiated the handling call. It is part b that has me stumped. After researching I've think the issue is that the calculation sub might need to be in a standard module (effectively creating a three modul solution of userform module + class module + standard module. I've made a few attempts at creating the "userform-->class module-->standard module-->userform" roundtrip to no avail. Help!
- First, how should the solution be architected?
- Second, how to I combine the texbox and combobox variants into a single class solution?
- Third, how do I call the various objects to work in harmony?
Here's my code.
Userform Module
Code:
Public EnableEvents As Boolean
Dim TB() As New tbClass
Private Sub UserForm_Initialize()
'load Paycheck helper form with gross pay from formIncome Adult 1
'Does not work
Dim tbCount As Integer
Dim ctl As Control
' Create the Textbutton objects
tbCount = 0
For Each ctl In formIncome.Controls
If TypeName(ctl) = "TextBox" Then
ReDim Preserve TB(1 To tbCount)
Set TB(tbCount).tbGroup = ctl
End If
Next ctl
me.EnableEvents = True
End Sub
Private Sub IncomeCalc()
'Sums up all income streams for both adults.
'Works
Me.EnableEvents = False
Dim MonthlyTakeHomePay As Double
Dim AnnualTakeHomePay As Double
Dim AnnualTakeHomeBonus As Double
Dim AnnualOtherIncome As Double
Dim AnnualIncomeTaxIncome As Double
Dim AnnualAdult1Income As Double
On Error Resume Next
For a = 1 To 2 'adult
For j = 1 To 4 'job
MonthlyTakeHomePay = Me.Controls("tbIncomeJob" & j & "Adult" & a & "MTakeHome_0").Value
AnnualTakeHomePay = MonthlyTakeHomePay * 12
AnnualTakeHomeBonus = Me.Controls("tbIncomeJob" & j & "Adult" & a & "Bonus_0").Value
AnnualOtherIncome = Me.Controls("tbIncomeJob" & j & "Adult" & a & "Other_0").Value
AnnualIncomeTaxIncome = Me.Controls("tbIncomeJob" & j & "Adult" & a & "Tax_0").Value
AnnualAdultIncome = AnnualTakeHomePay + AnnualTakeHomeBonus + AnnualOtherIncome + AnnualIncomeTaxIncome
Me.Controls("tbIncomeJob" & j & "Adult" & a & "Annual_0").Value = AnnualAdultIncome
Next j
Next a
Me.EnableEvents = True
End SubHere's the class module code
Code:
Public WithEvents tbGroup As MsForms.TextBox
'This class should excute code on formIncome or subIncomeCalc, whichever is determined as the go forward solution
Private Sub tbGroup_Click()
Run subIncomeCalc
End Sub
