Date update overflow error ‘6’

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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;

VBA 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;
VBA Code:
Sheets(“Worksheet”).Cells(y,1).Value = txtA
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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.

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

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I said earlier that you should not use the Change events to format the textboxes.
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

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

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

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I don't know which ones are dates, but for instance, in the update code:

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

and in the Plaka code:

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

Watch MrExcel Video

Forum statistics

Threads
1,130,127
Messages
5,640,275
Members
417,133
Latest member
caaronh85

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