Date update overflow error ‘6’

[Tayl4n]

Dear MrExcel community,
I have a project search & update & add & delete can do.
There are 32 column and 5 of them is date.
When I search date format has changed automatically “dd.mm.yyyy” to “mm/dd/yyyy” and shows up to textbox with that wrong format.
I solved this with this code;

txtU.Text = Format(txtU, “dd.mm.yyyy”

But this time update functions are broken for date textboxes. When I try to change a date on textbox this error shows up and date changin randomly ex. I press 2 and date changes to “01.01.1900”.
I feel confused. I use these codes for update;

Sheets(“Worksheet”).Cells(y,1).Value = txtA

 

[Tayl4n]

It's impossible for me to help you if I can't see the code you are using, I'm afraid.

Sorry for late. I wrote all codes again if there is any letter mistake please ignore because functions are work.

''''''Date columns''''''


Private Sub txtAA_Change()
txtAA.Text = Format(txtAA, "dd.mm.yyyy")
End Sub


Private Sub txtAC_Change()
txtAC.Text = Format(txtAC, "dd.mm.yyyy")
End Sub

Private Sub txtT_Change()
txtT.Text = Format(txtT, "dd.mm.yyyy")
End Sub

Private Sub txtU_Change()
txtU.Text = Format(txtU, "dd.mm.yyyy")
End Sub

Private Sub txtV_Change()
txtV.Text = Format(txtV, "dd.mm.yyyy")
End Sub



Private Sub cmdUpdate_Click()

'''''''''''For Update'''''''''''''''

Dim x As Long
Dim y As Long
x = Sheets("Worksheet").Range("K" & Rows.Count).End(xlUp).Row
For y = 2 To x


If Sheets("Worksheet").Cells(y, 11).Value = txtK.Text Then

Sheets("Worksheet").Cells(y, 11).Value = txtK

Sheets("Worksheet").Cells(y, 1).Value = txtA
Sheets("Worksheet").Cells(y, 2).Value = txtB
Sheets("Worksheet").Cells(y, 3).Value = txtC
Sheets("Worksheet").Cells(y, 4).Value = txtD
Sheets("Worksheet").Cells(y, 5).Value = txtE
Sheets("Worksheet").Cells(y, 6).Value = txtF
Sheets("Worksheet").Cells(y, 7).Value = txtG
Sheets("Worksheet").Cells(y, 8).Value = txtH
Sheets("Worksheet").Cells(y, 9).Value = txtI
Sheets("Worksheet").Cells(y, 10).Value = txtJ

Sheets("Worksheet").Cells(y, 12).Value = txtL
Sheets("Worksheet").Cells(y, 13).Value = txtM
Sheets("Worksheet").Cells(y, 14).Value = txtN
Sheets("Worksheet").Cells(y, 15).Value = txtO
Sheets("Worksheet").Cells(y, 16).Value = txtP
Sheets("Worksheet").Cells(y, 17).Value = txtQ
Sheets("Worksheet").Cells(y, 18).Value = txtR
Sheets("Worksheet").Cells(y, 19).Value = txtS
Sheets("Worksheet").Cells(y, 20).Value = txtT
Sheets("Worksheet").Cells(y, 21).Value = txtU
Sheets("Worksheet").Cells(y, 22).Value = txtV
Sheets("Worksheet").Cells(y, 23).Value = txtW
Sheets("Worksheet").Cells(y, 24).Value = txtX
Sheets("Worksheet").Cells(y, 25).Value = txtY
Sheets("Worksheet").Cells(y, 26).Value = txtZ
Sheets("Worksheet").Cells(y, 27).Value = txtAA
Sheets("Worksheet").Cells(y, 28).Value = txtAB
Sheets("Worksheet").Cells(y, 29).Value = txtAC
Sheets("Worksheet").Cells(y, 30).Value = txtAD
Sheets("Worksheet").Cells(y, 31).Value = txtAE
Sheets("Worksheet").Cells(y, 32).Value = txtAF


End If
Next y


''''''''''''''''''Clear Boxes'''''''''''''''''

Me.txtK.Value = ""
Me.txtA.Value = ""
Me.txtB.Value = ""
Me.txtC.Value = ""
Me.txtD.Value = ""
Me.txtE.Value = ""
Me.txtF.Value = ""
Me.txtG.Value = ""
Me.txtH.Value = ""
Me.txtI.Value = ""
Me.txtJ.Value = ""
Me.txtK.Value = ""
Me.txtL.Value = ""
Me.txtM.Value = ""
Me.txtN.Value = ""
Me.txtO.Value = ""
Me.txtP.Value = ""
Me.txtQ.Value = ""
Me.txtR.Value = ""
Me.txtS.Value = ""
Me.txtT.Value = ""
Me.txtU.Value = ""
Me.txtV.Value = ""
Me.txtW.Value = ""
Me.txtX.Value = ""
Me.txtY.Value = ""
Me.txtZ.Value = ""
Me.txtAA.Value = ""
Me.txtAB.Value = ""
Me.txtAC.Value = ""
Me.txtAD.Value = ""
Me.txtAE.Value = ""
Me.txtAF.Value = ""


MsgBox "Updated", vbInformation


End Sub


'''''''''''For Search'''''''''''''''

Sub cmdPlaka_Click()

Dim x As Long
Dim y As Long

x = Sheets("Worksheet").Range("K" & Rows.Count).End(xlUp).Row

txtK = Sheets("Worksheet").Cells(y, 11).Value

txtA = Sheets("Worksheet").Cells(y, 1).Value
txtB = Sheets("Worksheet").Cells(y, 2).Value
txtC = Sheets("Worksheet").Cells(y, 3).Value
txtD = Sheets("Worksheet").Cells(y, 4).Value
txtE = Sheets("Worksheet").Cells(y, 5).Value
txtF = Sheets("Worksheet").Cells(y, 6).Value
txtG = Sheets("Worksheet").Cells(y, 7).Value
txtH = Sheets("Worksheet").Cells(y, 8).Value
txtI = Sheets("Worksheet").Cells(y, 9).Value
txtJ = Sheets("Worksheet").Cells(y, 10).Value

txtL = Sheets("Worksheet").Cells(y, 12).Value
txtM = Sheets("Worksheet").Cells(y, 13).Value
txtN = Sheets("Worksheet").Cells(y, 14).Value
txtO = Sheets("Worksheet").Cells(y, 15).Value
txtP = Sheets("Worksheet").Cells(y, 16).Value
txtQ = Sheets("Worksheet").Cells(y, 17).Value
txtR = Sheets("Worksheet").Cells(y, 18).Value
txtS = Sheets("Worksheet").Cells(y, 19).Value
txtT = Sheets("Worksheet").Cells(y, 20).Value
txtU = Sheets("Worksheet").Cells(y, 21).Value
txtV = Sheets("Worksheet").Cells(y, 22).Value
txtW = Sheets("Worksheet").Cells(y, 24).Value
txtX = Sheets("Worksheet").Cells(y, 23).Value
txtY = Sheets("Worksheet").Cells(y, 25).Value
txtZ = Sheets("Worksheet").Cells(y, 26).Value
txtAA = Sheets("Worksheet").Cells(y, 27).Value
txtAB = Sheets("Worksheet").Cells(y, 28).Value
txtAC = Sheets("Worksheet").Cells(y, 29).Value
txtAD = Sheets("Worksheet").Cells(y, 30).Value
txtAE = Sheets("Worksheet").Cells(y, 31).Value
txtAF = Sheets("Worksheet").Cells(y, 32).Value


End If
Next y




End Sub










Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 33
ListBox1.List = Range("A2:AG750").Value


End Sub

 

[RoryA]

I said earlier that you should not use the Change events to format the textboxes.

 

[Tayl4n]

I said earlier that you should not use the Change events to format the textboxes.

This is currently codes. Other events didn’t work for format exit etc.. i tried

 

[RoryA]

If you use the Change event, it will be triggered by every single keystroke, so as soon as you type the first digit, it will try and convert that single number into a date. So, if you insist on using the Change event, you will need to add a test to make sure that whatever has been entered is a valid date first. It's honestly not worth the trouble.

Since you didn't show us what you tried with other events that didn't work, it really doesn't help us to help you.

 

[Tayl4n]

If you use the Change event, it will be triggered by every single keystroke, so as soon as you type the first digit, it will try and convert that single number into a date. So, if you insist on using the Change event, you will need to add a test to make sure that whatever has been entered is a valid date first. It's honestly not worth the trouble.

Since you didn't show us what you tried with other events that didn't work, it really doesn't help us to help you.

I worked with txtT textbox first I made it Exit event

Private Sub txtT_Exit(ByVal Cancel As MSForms.ReturnBoolean)

txtT.Text = Format(txtT, “dd.mm.yyyy”
End Sub

Result:
Date shows as “mm/dd/yyyy” editable.

Second event “Before Update”

Private Sub txtT_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

txtT.Text = Format(txtT, “dd.mm.yyyy”
End Sub

Date shows “mm/dd/yyyy” editable.
3rd event is “AfterUpdate”

Private Sub txtT_AfterUpdate(ByVal Cancel As MSForms.ReturnBoolean)

txtT.Text = Format(txtT, “dd.mm.yyyy”
End Sub

Date shows “mm/dd/yyyy” editable.
Any other event advice ?
If I change “Change Event” date format is broken but editable.

 

[RoryA]

Date shows “mm/dd/yyyy” editable

What exactly does that mean?

 

[Tayl4n]

What exactly does that mean?

Results from different events.
It means when I do event “Before Update”
Dates written on textbox with that format “mm/dd/yyyy” but I can edit without any problem.

 

[RoryA]

Your Plaka button code should format the dates before putting them into the textboxes. Your submit button code should use CDate when putting the dates back into cells.

 

[Tayl4n]

Your Plaka button code should format the dates before putting them into the textboxes. Your submit button code should use CDate when putting the dates back into cells.

Make sense. How can I edit the codes for this function ?

 

[RoryA]

I don't know which ones are dates, but for instance, in the update code:

Sheets("Worksheet").Cells(y, 1).Value = CDate(txtA)

and in the Plaka code:

txtA = Format$(Sheets("Worksheet").Cells(y, 1).Value, "dd.mm.yyyy")