• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Changes Unexpectedly in VBA UserForm When Using ControlSource Property to get Dates from Excel

jswindr

New Member
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 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:

jswindr

New Member
Hi, as there is nothin' to be in front of the scene ! …​
I am sorry if you are referring to the lack of enough info... I have realized that the over-simplified code shared above does not work as expected and is not enough to show what I am talking about.

More info:

1) I am from India even though I have noticed that Debug.Print Application.International(xlCountryCode) is showing 1 as output instead of 91 as I had expected.

2) Within worksheets, Excel is accepting dates as expected in the local date input format : dd/mm/yyyy

3) However, in VBA userforms, dates still need to be entered as mm/dd/yyyy (at least on my computer) to make Excel receive the date input as expected.

4) I am attaching a working copy of simplified version of the workbook which shows the issue I am facing in cases where some dates were being deliberately entered as 1-1-1900 (where actual dates were missing) to keep some other things in the workbook working.
 

Attachments

Marc L

Excel Ninja
  1. So bad Windows Regional Settings !

  2. Normal according to the cells formatting with real Excel dates (numbers) rather than texts …

  3. In fact the Excel engine natively understand only english / US dates
    whatever the local Excel version and whatever the Windows Regional Settings !

    So as an UserForm can not store any Excel date but just text
    so to convert an UserForm text according to the Windows Regional Settings to an Excel date
    you just must use the VBA function CDate

  4. But an important must read :
 

jswindr

New Member
Thank you so much Marc. This issue was driving me nuts.
So as an UserForm can not store any Excel date but just text
so to convert an UserForm text according to the Windows Regional Settings to an Excel date
you just must use the VBA function CDate
But would it be good idea to change text in the userform controls using vba code while still using the controlsource approach? I am afraid, this might change dates in the worksheet.
 

Marc L

Excel Ninja
If you wanna still use ControlSource then you should allocate the UserForm with dates as mm/dd rather than dd/mm​
and specify the format used in a comment within this UserForm …​
 

jswindr

New Member
you should allocate the UserForm with dates as mm/dd...
Exactly ! That's the only option I already thought I was left with. Thanks for the much needed reaffirmation.

However, to make doubly sure that correct input is made by users, in addition to showing format mm/dd/yyyy on the relevant labels accompanying the textboxes, I am also using ControlTips that display the input in dd-mmm-yyyy format, and optionally MsgBoxes that confirm the received input in the dd-mmm-yyyy format if it is a valid date, otherwise display "invalid date" along with the expected format mm/dd/yyyy.
 
Top