Solved: How do you modules to calculate?

[aziztt]

Ive created an income calculator to work out different frequency types of income but find it very tedious to copy and paste the same VBA code and change the end textbox name to another number. Is there a simple way to have one module i can put on each combobox that does the calculation.

I have worked the VBA code that works it all out and put it in a commandbutton, Im hoping the following VBA code could be a module instead for each combobox

[vba]Dim i As Long
Dim a As Long
'Defaults Frequency to Monthly
For i = 1 To 5
If (UserForm1.Controls("TextBox" & i) <> vbNullString) And _
(UserForm1.Controls("ComboBox" & i) = vbNullString) Then
UserForm1.Controls("ComboBox" & i).Text = "Monthly"
a = i + 20
UserForm1.Controls("TextBox" & a).Value = UserForm1.Controls("TextBox" & i).Value
a = 0
Else
End If
Next i

'Calulate Income
For i = 1 To 5
If (UserForm1.Controls("TextBox" & i) = vbNullString) And _
(UserForm1.Controls("ComboBox" & i) = vbNullString) Then
a = 0
a = 20 + i
UserForm1.Controls("TextBox" & a).Value = 0
Else
UserForm1.Controls("TextBox" & i).Value = _
Format((UserForm1.Controls("TextBox" & i).Value), "Currency")
a = 0
a = 20 + i
Select Case UserForm1.Controls("ComboBox" & i).Text
Case "Weekly"
UserForm1.Controls("TextBox" & a).Value = (UserForm1.Controls("TextBox" & i) * 52) / 12

Case "2 Weekly"
UserForm1.Controls("TextBox" & a).Value = ((UserForm1.Controls("TextBox" & i) / 2) * 52) / 12

Case "4 Weekly"
UserForm1.Controls("TextBox" & a).Value = ((UserForm1.Controls("TextBox" & i) / 4) * 52) / 12

Case "Monthly"
UserForm1.Controls("TextBox" & a).Value = UserForm1.Controls("TextBox" & i)

Case "Quarterly"
UserForm1.Controls("TextBox" & a).Value = (UserForm1.Controls("TextBox" & i) * 4) / 12

Case "Annualy"
UserForm1.Controls("TextBox" & a).Value = UserForm1.Controls("TextBox" & i) / 12
End Select
End If
Next i

'Calculate total
TextBox10.Value = 0
For i = 21 To 25
If UserForm1.Controls("TextBox" & i) = vbNullString Then
Else
TextBox10.Value = TextBox10.Value + CInt(UserForm1.Controls("TextBox" & i).Value)
TextBox10.Value = Format((TextBox10.Value), "Currency")
End If
Next i
End Sub[/vba]

[mdmackillop]

[vba]
Private Sub ComboBox1_Change()
DoStuff 1, 21, 10
End Sub

Private Sub ComboBox2_Change()
DoStuff 2, 22, 10
End Sub

Private Sub DoStuff(C1, C2, C3)

Dim Ctr1 As Control
Dim Ctr2 As Control
Dim Ctr3 As Control
Dim Ctr4 As Control

Set Ctr1 = Me.Controls("TextBox" & C1)
Set Ctr2 = Me.Controls("TextBox" & C2)
Set Ctr3 = Me.Controls("ComboBox" & C1)
Set Ctr4 = Me.Controls("TextBox" & C3)

If Ctr1.Text = vbNullString Then
MsgBox "Please enter amount first to choose frequency of income", vbApplicationModal, "Error"
Ctr3.Text = ""
Else

Select Case Ctr3.Text
Case "Weekly"
Ctr2.Value = (Ctr1.Value * 52) / 12
Case "2 Weekly"
Ctr2.Value = ((Ctr1.Value / 2) * 52) / 12
Case "4 Weekly"
Ctr2.Value = ((Ctr1.Value / 4) * 52) / 12
Case "Monthly"
Ctr2.Value = Ctr1.Value
Case "Quarterly"
Ctr2.Value = (Ctr1.Value * 4) / 12
Case "Annualy"
Ctr2.Value = Ctr1.Value / 12
End Select
Ctr2.Value = Format(Ctr2.Value, "Currency")
Ctr1.Value = Format(Ctr1.Value, "Currency")
End If

Ctr4.Value = 0

For i = 21 To 25
If UserForm1.Controls("TextBox" & i) = vbNullString Then
Else
Ctr4.Value = TextBox10.Value + CInt(UserForm1.Controls("TextBox" & i).Value)
Ctr4.Value = Format((Ctr4.Value), "Currency")
End If
Next i
End Sub

[/vba]

[aziztt]

Your a star!! looking at the solution you provided me it looks valid!

Going to implement it now, thanks for cutting time of copying and pasting similar code

[aziztt]

Works Flawless except Case "Monthly" should have one ".value" at the end of the case, thanks for your efforts

I still cant figure it out why the follwing code keeps looping three times with the same erroro message when no value entered when a combo item is chosen. It should show the error message just once.

[VBA]If Ctr1.Text = vbNullString Then
Ctr3.Value = vbNullString
MsgBox "Please enter amount first to choose frequency of income", vbApplicationModal, "Error"[/VBA]

It would be nice if it automatically tabbed to the relavent textbox to enter the amount corresponding to the comobox.

I have tried possible ways for example
[VBA]If Ctr1.Text = vbNullString Then
Ctr3.Value = vbNullString
MsgBox "Please enter amount first to choose frequency of income", vbApplicationModal, "Error"
GoTo Ctr1[/VBA]