![](/board/proxy.php?image=http%3A%2F%2Fi287.photobucket.com%2Falbums%2Fll152%2Fdutchvietnam%2Fdates%2520changing_zps8gnihicc.gif&hash=cc2acf0fdbcf0a9436cebc7ccc37793e)
I am having issue of the date format changing each time to combobox change event is triggered as per the gif.
This is my code for the on change event
Code:
Private Sub ComboBox1_Change()Dim r As Excel.Range
Set r = Worksheets("Sheet7").Range("D10:CI10").Find(What:=ComboBox1.Value, LookAt:=xlPart, MatchCase:=False)
If Not r Is Nothing Then
TextBox1.Text = r.Offset(1, 0).Value
TextBox2.Text = r.Offset(1, 1).Value
TextBox3.Text = r.Offset(1, 2).Value
TextBox4.Text = r.Offset(1, 3).Value
TextBox5.Text = r.Offset(1, 4).Value
TextBox6.Text = r.Offset(1, 5).Value
TextBox7.Text = r.Offset(1, 6).Value
End If
End Sub
And this is my code for the text box change event
Code:
Private Sub TextBox1_Change()If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("D11") = TextBox1.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("K11") = TextBox1.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("R11") = TextBox1.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("Y11") = TextBox1.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AF11") = TextBox1.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AM11") = TextBox1.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AT11") = TextBox1.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BA11") = TextBox1.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BH11") = TextBox1.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BO11") = TextBox1.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BV11") = TextBox1.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CC11") = TextBox1.Value
End If
End Sub
Private Sub TextBox2_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("E11") = TextBox2.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("L11") = TextBox2.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("S11") = TextBox2.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("Z11") = TextBox2.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AG11") = TextBox2.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AN11") = TextBox2.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AU11") = TextBox2.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BB11") = TextBox2.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BI11") = TextBox2.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BP11") = TextBox2.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BW11") = TextBox2.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CD11") = TextBox2.Value
End If
End Sub
Private Sub TextBox3_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("F11") = TextBox3.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("M11") = TextBox3.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("T11") = TextBox3.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AA11") = TextBox3.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AH11") = TextBox3.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AO11") = TextBox3.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AV11") = TextBox3.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BC11") = TextBox3.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BJ11") = TextBox3.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BQ11") = TextBox3.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BX11") = TextBox3.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CE11") = TextBox3.Value
End If
End Sub
Private Sub TextBox4_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("G11") = TextBox4.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("N11") = TextBox4.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("U11") = TextBox4.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AB11") = TextBox4.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AI11") = TextBox4.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AP11") = TextBox4.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AW11") = TextBox4.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BD11") = TextBox4.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BK11") = TextBox4.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BR11") = TextBox4.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BY11") = TextBox4.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CF11") = TextBox4.Value
End If
End Sub
Private Sub TextBox5_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("H11") = TextBox5.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("O11") = TextBox5.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("V11") = TextBox5.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AC11") = TextBox5.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AJ11") = TextBox5.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AQ11") = TextBox5.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AX11") = TextBox5.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BE11") = TextBox5.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BL11") = TextBox5.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BS11") = TextBox5.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BZ11") = TextBox5.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CG11") = TextBox5.Value
End If
End Sub
Private Sub TextBox6_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("I11") = TextBox6.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("P11") = TextBox6.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("W11") = TextBox6.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AD11") = TextBox6.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AK11") = TextBox6.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AR11") = TextBox6.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AY11") = TextBox6.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BF11") = TextBox6.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BM11") = TextBox6.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BT11") = TextBox6.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("CA11") = TextBox6.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CH11") = TextBox6.Value
End If
End Sub
Private Sub TextBox7_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("J11") = TextBox7.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("Q11") = TextBox7.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("X11") = TextBox7.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AE11") = TextBox7.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AL11") = TextBox7.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AS11") = TextBox7.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AZ11") = TextBox7.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BG11") = TextBox7.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BN11") = TextBox7.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BU11") = TextBox7.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("CB11") = TextBox7.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CI11") = TextBox7.Value
End If
End Sub
Any help is much appreciated!!