Values not updating in TextBox on change in value in Combobox

[Ankit Jain]

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

 

[dmt32]

Hi

Welcome to forum

If need your textboxes to update / change state in relation to other control value changes then probably better to have your code in one place & have the appropriate change events call it.

Not fully tested but see if following does what you want

Make backup of your workbook & ensure that you delete any existing codes.

Copy ALL code to user forms code page

Private Sub ComboBox2_Change()
    CalcValues
End Sub

Private Sub TextBox4_Change()
    CalcValues
End Sub

Private Sub TextBox8_Change()
    CalcValues
End Sub

Private Sub TextBox9_Change()
    CalcValues
End Sub

Sub CalcValues()
    Dim TextBoxDate As Variant
    Dim CalcTextBox As Double
    Dim Index As Integer
   
    Index = Me.ComboBox2.ListIndex + 1
   
    Me.TextBox10.Enabled = CBool(Index > 1)
    Me.TextBox13.Enabled = CBool(Index > 2)
    Me.TextBox14.Enabled = CBool(Index > 2)
   
    CalcTextBox = Val(TextBox4.Value) * Val(TextBox8.Value)
    Me.TextBox12.Value = IIf(CalcTextBox > 0, CalcTextBox / Choose(Index, 100, 200, 400), 0)
   
    TextBoxDate = Me.TextBox9.Value
    If IsDate(TextBoxDate) Then TextBoxDate = DateValue(TextBoxDate) Else TextBoxDate = Date: Index = 1
   
    If Index < 1 Then Index = 1
    TextBox10.Value = IIf(Index > 1, Format(DateAdd("M", Choose(Index, 0, 6, 3), TextBoxDate), "dd-mmm-yyyy"), "")
    TextBox13.Value = IIf(Index > 2, Format(DateAdd("M", 9, TextBoxDate), "dd-mmm-yyyy"), "")
    TextBox14.Value = IIf(Index > 2, Format(DateAdd("M", 6, TextBoxDate), "dd-mmm-yyyy"), "")
End Sub

Others here may have alternative solutions

Hope Helpful


Dave