Hi
I am a new VBA user and working on a project. I made two two macros i.e. models. In the beginning I wanted to ask the user to decide which model to run. So when user enters 1 the model 1 runs fine right till the end when I entered a msg box whether he want to recalculate. Now here is the problem. If I click yes then everything works accordingly that it runs all over again but if I click 'No' it should end it there. But it reruns the model 1. I am not sure what is the problem.
Here is my code
Code
Sub model()
modelchoice = InputBox("What model to run?")
If modelchoice = 1 Then
Run ([Retirementincome()])
Else: modelchoice = 2
Run ([Semiannualsavings()])
End If
End Sub
Sub Retirementincome()
RetirementcalculatorA:
Cells.Clear
Dim PMTbeforetirement, CurrentAge As Integer, RetirementAge As Integer, lifeexpat As Integer, rate, FVatretirement, PMTafterretirement, Monthlyincome
PMTbeforeretirement = InputBox("What is the amount to be saved semi annualy?", "Enter $ Amount")
Cells(1, 1).Value = "Semi annual savings till retirement"
Cells(1, 2).Value = PMTbeforeretirement
Cells(1, 2).NumberFormat = "$#,##0.00"
CurrentAge:
CurrentAge = InputBox("What is your current age in years?", "Enter current age in years between 18 and 65")
If CurrentAge >= 18 And CurrentAge <= 65 Then
Cells(2, 1).Value = "Current Age"
Cells(2, 2).Value = CurrentAge
Else: GoTo CurrentAge
End If
RetirementAge:
RetirementAge = InputBox("When is your retirement age in years?", "Enter retirement age in years between 55 and 75")
If RetirementAge >= 55 And RetirementAge <= 75 Then
Cells(3, 1).Value = "Retirement Age"
Cells(3, 2).Value = RetirementAge
Else: GoTo RetirementAge
End If
lifeexpat:
lifeexpat = InputBox("What is your life expactancy in years?", "Enter life expactancy in years between Retirement age and 100")
If lifeexpat >= RetirementAge And RetirementAge <= 100 Then
Cells(4, 1).Value = "Life Expactancy"
Cells(4, 2).Value = lifeexpat
Else: GoTo lifeexpat
End If
rate:
rate = InputBox("What is the interest rate?", "Enter interest rate as percentage points Hint:interest rate should be positive")
If rate >= 0 Then
Cells(5, 1).Value = "Interest Rate"
Cells(5, 2).Value = rate / 100
Cells(5, 2).NumberFormat = "0.00%"
Else: GoTo rate
End If
confirm = MsgBox("Please confirm,Semi Annual Savings=" & PMTbeforeretirement & ", Current Age=" & CurrentAge & ", Retirement Age=" & RetirementAge & ", Life Expactancy=" & lifeexpat & ", Interest Rate=" & rate & "%", vbYesNo, "Please confirm all inputs")
If confirm = 6 Then
FVatretirement = -FV(Cells(5, 2).Value / 2, (Cells(3, 2).Value - Cells(2, 2).Value) * 2, Cells(1, 2).Value)
Cells(6, 1).Value = "Future value at retirement"
Cells(6, 2).Value = FVatretirement
Cells(6, 2).NumberFormat = "$#,##0.00"
PMTafterretirement = Pmt(Cells(5, 2).Value / 2, (Cells(4, 2).Value - Cells(3, 2).Value) * 2, -Cells(6, 2).Value)
Cells(7, 1).Value = "Semi annual payments after retirements till death"
Cells(7, 2).Value = PMTafterretirement
Cells(7, 2).NumberFormat = "$#,##0.00"
Monthlyincome = PMTafterretirement / 6
Cells(8, 1).Value = "Monthly retirement income till death"
Cells(8, 2).Value = Monthlyincome
Cells(8, 2).NumberFormat = "$#,##0.00"
Columns("A").AutoFit
Columns("B").AutoFit
End If
again = MsgBox("Do you want to recalculate?", vbYesNo)
If again = 6 Then
Run ([model()])
Else:
Exit Sub
End If
End Sub
Sub Semiannualsavings()
RetirementcalculatorB:
Cells.Clear
Dim PMTbeforetirement, CurrentAge As Integer, RetirementAge As Integer, lifeexpat As Integer, rate, PVatretirement, PMTafterretirement, Monthlysavings
PMTafterretirement:
PMTafterretirement = InputBox("What is the amount you want after retirement semi annualy? Amount should be greater then $12000 i.e. $2000 per month", "Enter $ Amount")
If PMTafterretirement >= 12000 Then
Cells(1, 1).Value = "Semi annual Retirement Income "
Cells(1, 2).Value = PMTafterretirement
Cells(1, 2).NumberFormat = "$#,##0.00"
Else: GoTo PMTafterretirement
End If
CurrentAge:
CurrentAge = InputBox("What is your current age in years?", "Enter current age in years between 18 and 65")
If CurrentAge >= 18 And CurrentAge <= 65 Then
Cells(2, 1).Value = "Current Age"
Cells(2, 2).Value = CurrentAge
Else: GoTo CurrentAge
End If
RetirementAge:
RetirementAge = InputBox("When is your retirement age in years?", "Enter retirement age in years between 55 and 75")
If RetirementAge >= 55 And RetirementAge <= 75 Then
Cells(3, 1).Value = "Retirement Age"
Cells(3, 2).Value = RetirementAge
Else: GoTo RetirementAge
End If
lifeexpat:
lifeexpat = InputBox("What is your life expactancy in years?", "Enter life expactancy in years between Retirement age and 100")
If lifeexpat >= RetirementAge And RetirementAge <= 100 Then
Cells(4, 1).Value = "Life Expactancy"
Cells(4, 2).Value = lifeexpat
Else: GoTo lifeexpat
End If
rate:
rate = InputBox("What is the interest rate?", "Enter interest rate as percentage points")
If rate >= 0 Then
Cells(5, 1).Value = "Interest Rate"
Cells(5, 2).Value = rate / 100
Cells(5, 2).NumberFormat = "0.00%"
Else: GoTo rate
End If
confirm = MsgBox("Please confirm,Semi Annual Retirement Income=" & PMTafterretirement & ", Current Age=" & CurrentAge & ", Retirement Age=" & RetirementAge & ", Life Expactancy=" & lifeexpat & ", Interest Rate=" & rate & "%", vbYesNo, "Please confirm all inputs")
If confirm = 6 Then
PVatretirement = PV(Cells(5, 2).Value / 2, (Cells(4, 2).Value - Cells(3, 2).Value) * 2, -Cells(1, 2).Value)
Cells(6, 1).Value = "Present Value at retirement"
Cells(6, 2).Value = PVatretirement
Cells(6, 2).NumberFormat = "$#,##0.00"
PMTbeforeretirement = Pmt(Cells(5, 2).Value / 2, (Cells(3, 2).Value - Cells(2, 2).Value) * 2, -Cells(6, 2).Value)
Cells(7, 1).Value = "Semi Annual Savings till retirement "
Cells(7, 2).Value = PMTbeforeretirement
Cells(7, 2).NumberFormat = "$#,##0.00"
Monthlysavings = PMTbeforeretirement / 6
Cells(8, 1).Value = "Monthly Savings till Retirement"
Cells(8, 2).Value = Monthlysavings
Cells(8, 2).NumberFormat = "$#,##0.00"
Columns("A").AutoFit
Columns("B").AutoFit
End If
again = MsgBox("Do you want to recalculate?", vbYesNo)
If again = 6 Then
Run ([model()])
End If
End Sub
Any help would be highly appreciated.
Thanks.
Display More