**Windows 7, Excel 2010**
I have a spreadsheet with one sheet set up as a userform to enter records and navigate records with button macros.
In cell (name: DT_Formdate) user enters date and Worksheet_Change event puts that date in the appropriate row in another hidden sheet containing all the records (set up like single-table database).
There are two buttons that allow user to go forward and backward through the records and the userform is updated accordingly.
'Code has been simplified to just the relevant data.
Now, if I set the Excel Options to Date System 1900, then I have no problems entering dates, navigating through records and dates changing accordingly, and changing dates.
But, with the Date System set to 1904, I have problems. Here is a chronological example of what I might do and what happens.
Enter Excel Options, set Date System to 1904
Use navigation buttons to go to record #1
Enter "5/9/1980" into date field
The corresponding record in the other sheet shows "5/10/1984"!!! (I'm using Excel Table to hold data)
Navigate to record #2
Enter "8/27/2012" into date field
The corresponding record in the other sheet shows "8/28/2016"!!!
Use navigation button to go back to record #1
Now the date field (which should have held "5/9/1980" for record #1 shows "5/11/1988"!!! (it increases by 4 years 1 day each time the cell is accessed.)
Go back to record #2
The user sheet and data sheet now both show "8/29/2020" where "8/27/2012" were entered!!!
Okay, keep in mind that there are NO problems when using the 1900 date system.
Any ideas? The only macros making changes to these cells are listed above.
I have a spreadsheet with one sheet set up as a userform to enter records and navigate records with button macros.
In cell (name: DT_Formdate) user enters date and Worksheet_Change event puts that date in the appropriate row in another hidden sheet containing all the records (set up like single-table database).
There are two buttons that allow user to go forward and backward through the records and the userform is updated accordingly.
Code:
Sub NextDTRecord()
If [DT_RecordNumber] < Data.Range("tblDT_Data").Rows.Count Then
[DT_RecordNumber] = [DT_RecordNumber].Value + 1
Call UpdateDisplayedRecord
[DT_FormHandler].Select
End If
End Sub
Sub PreviousDTRecord()
If [DT_RecordNumber] > 1 Then
[DT_RecordNumber] = [DT_RecordNumber].Value - 1
Call UpdateDisplayedRecord
[DT_FormHandler].Select
End If
End Sub
Sub UpdateDisplayedRecord()
[DT_FormDate] = [DT_TrainingDate].Offset([DT_RecordNumber].Value, 0)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Set ws = DetectionTraining
Select Case Target.Address
Case "$H$6" 'Change to Training Date
[DT_TrainingDate].Offset([DT_RecordNumber].Value, 0) = [DT_FormDate]
End Select
End Sub
'Code has been simplified to just the relevant data.
Now, if I set the Excel Options to Date System 1900, then I have no problems entering dates, navigating through records and dates changing accordingly, and changing dates.
But, with the Date System set to 1904, I have problems. Here is a chronological example of what I might do and what happens.
Enter Excel Options, set Date System to 1904
Use navigation buttons to go to record #1
Enter "5/9/1980" into date field
The corresponding record in the other sheet shows "5/10/1984"!!! (I'm using Excel Table to hold data)
Navigate to record #2
Enter "8/27/2012" into date field
The corresponding record in the other sheet shows "8/28/2016"!!!
Use navigation button to go back to record #1
Now the date field (which should have held "5/9/1980" for record #1 shows "5/11/1988"!!! (it increases by 4 years 1 day each time the cell is accessed.)
Go back to record #2
The user sheet and data sheet now both show "8/29/2020" where "8/27/2012" were entered!!!
Okay, keep in mind that there are NO problems when using the 1900 date system.
Any ideas? The only macros making changes to these cells are listed above.