Displaying Normal Date formatb to a textbox instead of the searial value of the date

sacchiisNoob · Apr 16th 2016

Hi im a noob when i comes to VBA and this is my first time to create a program. i have been beating my brains out for days now . I would like to ask if any of you knows how to make the textbox display the normal date format like dd/mm/yyyy coz every time i try to retrieve the whole worksheet all the textbox for my date displays serial values like 30698 ( 01/17/1984). I have done the format cell still the same result. and when i press CTL~
it shows that my date columns are in serial value. is there a code that can help display the right date format? or do i need to tweek something in my excel worksheet to change it? thank you in advance.
[ATTACH=CONFIG]68875[/ATTACH]

sktneer · Apr 16th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

How do you retrieve the cell value into the TextBox? What code do you use?

sacchiisNoob · Apr 17th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

hi sktneer
this is the code i used in retrieving my datas

Code

Private Sub lstSearch_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'dim the variables
Dim i As Integer
On Error Resume Next
'find the selected list item
i = Me.lstSearch.ListIndex
'add the values to the text boxes
Me.tbEmpNo.value = Me.lstSearch.Column(0, i)
Me.tbFamName.value = Me.lstSearch.Column(1, i)
Me.tbFirstName.value = Me.lstSearch.Column(2, i)
Me.tbMidName.value = Me.lstSearch.Column(3, i)
Me.tbConNum.value = Me.lstSearch.Column(4, i)
Me.tbAge.value = Me.lstSearch.Column(5, i)
Me.tbPresAdd.value = Me.lstSearch.Column(6, i)
Me.tbProvAdd.value = Me.lstSearch.Column(7, i)
Me.tbdob.value = Me.lstSearch.Column(8, i)
Me.tbpob.value = Me.lstSearch.Column(9, i)
Me.tbsss.value = Me.lstSearch.Column(10, i)
Me.tbph.value = Me.lstSearch.Column(11, i)
Me.tbpn.value = Me.lstSearch.Column(12, i)
Me.tbtin.value = Me.lstSearch.Column(13, i)
Me.tbsgl.value = Me.lstSearch.Column(14, i)
Me.tbdi.value= Me.lstSearch.Column(15, i)
Me.tbed.value = Me.lstSearch.Column(16, i)
Me.cbEduc.value = Me.lstSearch.Column(17, i)
Me.tbCourse.value = Me.lstSearch.Column(18, i)
Me.cbPre1.value = Me.lstSearch.Column(19, i)
Me.cbPre2.value = Me.lstSearch.Column(20, i)
Me.cbPrev1.value = Me.lstSearch.Column(21, i)
Me.cbPrev2.value = Me.lstSearch.Column(22, i)
Me.tbSkills.value = Me.lstSearch.Column(23, i)
Me.tbdh.value = Me.lstSearch.Column(24, i)
'reset error handler
On Error GoTo 0
End Sub

Display More

sktneer · Apr 17th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

A sample workbook would be helpful to see what controls you have on the userform and how you are transferring the values from the sheet into the controls or from userform into the sheet.

sacchiisNoob · Apr 17th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

hi sorry for the incomplete details here is the project im working on atm
https://www.dropbox.com/s/1umhutug0mpw50r/Book1.xlsm?dl=0
i hope this would be helpful. thank you

**pike** · Apr 17th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

Hi
you can format the serial number as date like

Code

Me.tbed.Value = Format(Me.lstSearch.Column(16, i), "dd/mm/yy")

sacchiisNoob · Apr 17th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

nice! i will definitely try that thank you so much!

sacchiisNoob · Apr 17th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

oh my gosh it worked!! thank u so so so much Pike and thank you too sktneer u guys are awesome

sktneer · Apr 17th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

Another way to populate the TextBox with the date is as below....

Code

Me.tbdob.Value = CDate(Me.lstSearch.Column(8, i))

**pike** · Apr 17th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

of course the CDate function is a better way to go and the format function for if you want to change the default date setting

sktneer · Apr 18th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

Yes you are correct. Good point.
Actually CDate will adopt the date format as per the system's date setting so if the format is set to m/d/yyyy in regional settings, CDate will also display the date in that format.
It will not work if you specifically want the date to display in the d/m/yyy yformat.

sacchiisNoob · Apr 18th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

wow i learned something new thank u guys i would definitely take note of that

sktneer · Apr 18th 2016

Re: Displaying Normal Date formatb to a textbox instead of the searial value of the d

Glad you found the discussion helpful.

Displaying Normal Date formatb to a textbox instead of the searial value of the date

Participate now!

Share

Users Viewing This Thread