Ankit Jain
New Member
- Joined
- May 25, 2020
- Messages
- 1
- Office Version
- 2013
- Platform
- Windows
I am creating a userform in excel for keeping a record of Bonds. The userform includes various data like interest rate, interest frequency, interest due date etc. following are the other details for the userform:
TextBox8 = Interest Rate
TextBox27=Price Per Unit
TextBox4=Face Value
TextBox5=Cost
TextBox9=First Interest Due Date
TextBox10=Second Interest Due Date
TextBox13=Third Interest Due Date
TextBox14=Fourth Interest Due Date
TextBox12=Interest Amount
Combobox2=Interest Frequency (Annual, Half Yearly,Quarterly)
I have written following VBA Code for updating the values of textbox automatically:
Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox2.Value = "Annual" Then
TextBox12.Value = (TextBox4.Value * TextBox8.Value) / 100
TextBox10.Enabled = False
TextBox13.Enabled = False
TextBox14.Enabled = False
End If
If ComboBox2.Value = "Half Yearly" Then
TextBox12.Value = (TextBox4.Value * TextBox8.Value) / 200
TextBox13.Enabled = False
TextBox14.Enabled = False
End If
If ComboBox2.Value = "Quarterly" Then
TextBox12.Value = (TextBox4.Value * TextBox8.Value) / 400
End If
End Sub
For automatically adding Interest Due Date
Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox2.Value = "Annual" Then
TextBox10.Value = ""
TextBox13.Value = ""
TextBox14.Value = ""
ElseIf ComboBox2.Value = "Half Yearly" Then
TextBox10.Value = Format(DateAdd("M", 6, TextBox9.Value), "dd-mmm-yyyy")
TextBox13.Value = ""
TextBox14.Value = ""
ElseIf ComboBox2.Value = "Quarterly" Then
TextBox10.Value = Format(DateAdd("M", 3, TextBox9.Value), "dd-mmm-yyyy")
TextBox14.Value = Format(DateAdd("M", 6, TextBox9.Value), "dd-mmm-yyyy")
TextBox13.Value = Format(DateAdd("M", 9, TextBox9.Value), "dd-mmm-yyyy")
End If
End Sub
1. If all the values are filled properly, then the userform is working properly but if I am selecting the value of combobox directly without filling any value in other textbox it is showing error.
2. On filling the userform afresh the code is working properly. But on updating values in combobox in previously filled userform, interest due date textbox are not disabling accordingly.
For example, while filing userform afresh, if quarterly is selected in the combobox, all the interest due date textbox are properly showing date but if in the same form quarterly is deselected and annual is selected the code is not running properly.
Please provide solution for above 2 problems
TextBox8 = Interest Rate
TextBox27=Price Per Unit
TextBox4=Face Value
TextBox5=Cost
TextBox9=First Interest Due Date
TextBox10=Second Interest Due Date
TextBox13=Third Interest Due Date
TextBox14=Fourth Interest Due Date
TextBox12=Interest Amount
Combobox2=Interest Frequency (Annual, Half Yearly,Quarterly)
I have written following VBA Code for updating the values of textbox automatically:
Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox2.Value = "Annual" Then
TextBox12.Value = (TextBox4.Value * TextBox8.Value) / 100
TextBox10.Enabled = False
TextBox13.Enabled = False
TextBox14.Enabled = False
End If
If ComboBox2.Value = "Half Yearly" Then
TextBox12.Value = (TextBox4.Value * TextBox8.Value) / 200
TextBox13.Enabled = False
TextBox14.Enabled = False
End If
If ComboBox2.Value = "Quarterly" Then
TextBox12.Value = (TextBox4.Value * TextBox8.Value) / 400
End If
End Sub
For automatically adding Interest Due Date
Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox2.Value = "Annual" Then
TextBox10.Value = ""
TextBox13.Value = ""
TextBox14.Value = ""
ElseIf ComboBox2.Value = "Half Yearly" Then
TextBox10.Value = Format(DateAdd("M", 6, TextBox9.Value), "dd-mmm-yyyy")
TextBox13.Value = ""
TextBox14.Value = ""
ElseIf ComboBox2.Value = "Quarterly" Then
TextBox10.Value = Format(DateAdd("M", 3, TextBox9.Value), "dd-mmm-yyyy")
TextBox14.Value = Format(DateAdd("M", 6, TextBox9.Value), "dd-mmm-yyyy")
TextBox13.Value = Format(DateAdd("M", 9, TextBox9.Value), "dd-mmm-yyyy")
End If
End Sub
1. If all the values are filled properly, then the userform is working properly but if I am selecting the value of combobox directly without filling any value in other textbox it is showing error.
2. On filling the userform afresh the code is working properly. But on updating values in combobox in previously filled userform, interest due date textbox are not disabling accordingly.
For example, while filing userform afresh, if quarterly is selected in the combobox, all the interest due date textbox are properly showing date but if in the same form quarterly is deselected and annual is selected the code is not running properly.
Please provide solution for above 2 problems