Hi,
I am facing a strange problem when using ControlSource property of textbox while working with dates in Excel VBA.
I have an Excel Worksheet which includes a few columns for dates. In several cases, dates are either not known or need to be confirmed. In those cases, for some reason, the date have been entered as
For the userform in VBA, I have used ControlSource property of textbox which helps me get data from Excel, and then save it automatically (without using any save button) when changes are made in VBA userform. Everything is running fine as per the expectations, except for this strange behavior in case of dates.
Date
Here is a simplified version of the VBA code which is being used to fetch and write data from and to the worksheet:
What is going on behind the scenes, and how do I make it work as expected?
I am facing a strange problem when using ControlSource property of textbox while working with dates in Excel VBA.
I have an Excel Worksheet which includes a few columns for dates. In several cases, dates are either not known or need to be confirmed. In those cases, for some reason, the date have been entered as
1-1-1900
which displays as 1-Jan-1900
on the worksheet as per the applied format (dd-mmm-yyyy
)For the userform in VBA, I have used ControlSource property of textbox which helps me get data from Excel, and then save it automatically (without using any save button) when changes are made in VBA userform. Everything is running fine as per the expectations, except for this strange behavior in case of dates.
Date
1-1-1900
entered in several places in the Excel Workbook (as described above) is getting fetched in VBA userform as 12/31/1899
. Even if change it to 1/1/1900 in the form, it automatically changes back to 12/31/1899
, and in Excel it keeps on showing such dates as 1-1-1900
.Here is a simplified version of the VBA code which is being used to fetch and write data from and to the worksheet:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
frm.Show
End Sub
Private Sub frm_Activate()
Call setControlSources (ActiveCell)
End Sub
Private Sub setControlSources (clientNo_cell As Range)
txtDate1.ControlSource = "TEST_Sheet!E" & clientNo_Cell.Row
'txtDate2.ControlSource = "TEST_Sheet!F" & clientNo_Cell.Row
'txtDate3.ControlSource = "TEST_Sheet!G" & clientNo_Cell.Row
'txtDate4.ControlSource = "TEST_Sheet!H" & clientNo_Cell.Row
End Sub
What is going on behind the scenes, and how do I make it work as expected?
Last edited: