aziztt
08-19-2008, 08:26 AM
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
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
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
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