Entering data in a user form is easy. With option an command buttons along with text boxes we can create a user form with multiple functions. Here we've created a form to calculate compound interest and equal monthly instalments and also transfer the data to an appropriate Excel worksheet.
The complete source code is given below.
Private Sub CommandButton1_Click()
Dim amount, interest, period, PMT, EMI As Double
Sheet1.Activate
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
amount = Val(TextBox1.Text)
Cells(eRow, 1) = amount
interest = Val(TextBox2.Text) / 1200
Cells(eRow, 2) = interest
period = Val(TextBox3.Text) * 12
Cells(eRow, 3) = period
'EMI = -PMT(interest / 12, period * 12, amount)
EMI = WorksheetFunction.PMT(interest, period, amount)
TextBox4.Text = -EMI
Cells(eRow, 4) = -EMI
End Sub
Private Sub CommandButton2_Click()
Dim amount, interest, period, intacc As Double
Sheet2.Activate
eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
amount = Val(TextBox1.Text)
Cells(eRow, 1) = amount
interest = Val(TextBox2.Text) / 100
Cells(eRow, 2) = interest
period = Val(TextBox3.Text)
Cells(eRow, 3) = period
intacc = ((amount) * (1 + interest) ^ period) - amount
Cells(eRow, 4) = intacc
TextBox4.Text = Round(intacc, 2)
End Sub
Private Sub CommandButton3_Click()
Label1.Caption = ""
Label2.Caption = ""
Label3.Caption = ""
Label4.Caption = ""
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox1.SetFocus
End Sub
Private Sub CommandButton4_Click()
If MsgBox("Quit?", vbYesNo) = vbYes Then End
End Sub
Private Sub Label5_Click()
End Sub
Private Sub OptionButton1_Click()
If OptionButton1 = True Then
Label1.Caption = "Loan Amount, Eg. 20000"
Label1.Font.Bold = True
Label2.Caption = "Interest Rate, Eg. 15"
Label2.Font.Bold = True
Label3.Caption = "Period in Years, Eg. 15"
Label3.Font.Bold = True
Label4.Caption = "Equal Monthly Payments"
Label4.Font.Bold = True
TextBox1.SetFocus
End If
End Sub
Private Sub OptionButton2_Click()
If OptionButton2 = True Then
Label1.Caption = "Amount Invested, Eg. 20000"
Label1.Font.Bold = True
Label2.Caption = "Interest Rate, Eg. 7"
Label2.Font.Bold = True
Label3.Caption = "Period in Years, Eg. 5"
Label3.Font.Bold = True
Label4.Caption = "Interest Accrued"
Label4.Font.Bold = True
TextBox1.SetFocus
End If
End Sub
Private Sub UserForm_Click()
TextBox1.SetFocus
End Sub
The code is really not difficult to understand. With some practice you should be able to create forms for other financial functions like FV, NPV, etc. In fact, you could create forms to perform income and expense entries and finally calculate your profit based on vouchers!
|