Values not updating in TextBox on change in value in Combobox

Ankit Jain

New Member
Joined
May 25, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. 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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,487
Office Version
  1. 2019
Platform
  1. Windows
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


VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,859
Messages
5,627,294
Members
416,237
Latest member
Tethys

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top