Date format in userform text box on double click.

R

Rfriend

Board Regular
Joined
May 10, 2021
Messages
73
Office Version
  1. 2010
Platform
  1. Windows
Hello All,

I am not having any luck researching a specific issue and have tried so many different tutorials, but I am still unable to get the results I want.

Problem:

I have a userform with a listbox that is tied to a double click event. When you double click the record returned from a search the data populates user controls for editing. I have two text boxes for date fields mm/dd/yyyy. When I search I see the correct dates, after calling the data I get the MS date code instead of the desired format. I have used both the "before and after update," on exit, and on initialize. I have tried both "cdate and format" as well, but the data still appears in the textbox in MS date format upon double clicking the desired record to load. You can type over the MS format or just space bar on the end of the code and tab off, the date will convert to the correct format. How do I get the date to call as a date format, rather than as a MS format?

Capture.GIF



VBA Code: 
Private Sub Emp27_AfterUpdate()
    On Error Resume Next
    Me.Emp27.Value = Format(Me.Emp27, "mm/dd/yy")
End Sub

Private Sub Emp28_AfterUpdate()
    On Error Resume Next
    Me.Emp28.Value = Format(Me.Emp28, "mm/dd/yy")
End Sub

Private Sub Userform_Initialize()
    Me.Emp28.Value = Format(Me.Emp28, "mm/dd/yy")
    Me.Emp27.Value = Format(Me.Emp27, "mm/dd/yy")
    Me.lstEmp.RowSource = ""
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Rfriend said:
How do I get the date to call as a date format, rather than as a MS format?
Click to expand...

Hi,
Use the TEXT property of the Range to return what you see in the cell rather than its underlying value

example

Rich (BB code): 
Me.Emp27.Value = Cells(1,10).TEXT

Dave
 
Upvote 0
dmt32 said:
Hi,
Use the TEXT property of the Range to return what you see in the cell rather than its underlying value

example

Rich (BB code): 
Me.Emp27.Value = Cells(1,10).TEXT

Dave
Click to expand...
Thank you for the reply, however this has not made any change to the output. Again, I do not have a problem with the date displaying in the record, the date displaying as a date when typed, or the data displaying as a date when making a change in the displayed textbox i.e. adding a space at the end of the displayed MS formatted date or retyping the date with "afterupdate". Just when I double click a record with a date populating the textbox control with a date format.
 

Attachments

  • Capture.GIF
    Capture.GIF
    70.8 KB · Views: 11
Upvote 0
Rfriend said:
...Just when I double click a record with a date populating the textbox control with a date format.
Click to expand...
Your explanation is unclear and a bit confusing. Can you post your double click event code?
 
Upvote 0
dmt32 said:
can you share the code that populates the TextBox

Dave
Click to expand...
Sure, and again, thank you.

VBA Code: 
Private Sub cmdContact_Click()
    Dim Crit As Range
    Dim FindMe As Range
    Dim Staff_DataSH As Worksheet
        On Error GoTo errHandler:
    Set Staff_DataSH = Sheet7
        Application.ScreenUpdating = False
    If Me.cboHeader.Value <> "All_Columns" Then
    If Me.txtSearch = "" Then
        Staff_DataSH.Range("E6") = ""
    Else
        Staff_DataSH.Range("E6") = "*" & Me.txtSearch.Value & "*"
    End If
    End If
    If Me.cboHeader.Value = "All_Columns" Then
        Set FindMe = Staff_DataSH.Range("A9:AH100000").Find(What:=txtSearch, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        Set Crit = Staff_DataSH.Cells(8, FindMe.Column)
    If Me.txtSearch = "" Then
        Staff_DataSH.Range("E6") = ""
        Staff_DataSH.Range("E5") = ""
    Else
        Staff_DataSH.Range("E5") = Crit
    If Crit = "ID" Then
        Staff_DataSH.Range("E6") = Me.txtSearch.Value
    Else
        Staff_DataSH.Range("E6") = "*" & Me.txtSearch.Value & "*"
    End If
        Me.txtAllColumn = Staff_DataSH.Range("E5").Value
    End If
    End If
        AdvFilterOutdata
        lstEmployee.RowSource = Staff_DataSH.Range("Outdata").Address(external:=True)
        On Error GoTo 0
    Exit Sub
errHandler:
        MsgBox "No match found for " & txtSearch.Text
        Me.lstEmployee.RowSource = ""
    Exit Sub
End Sub

Private Sub lstEmployee_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Integer
        On Error Resume Next
      i = Me.lstEmployee.ListIndex
        Me.Emp1.Value = Me.lstEmployee.Column(0, i)
        Me.Emp2.Value = Me.lstEmployee.Column(3, i)
        Me.Emp3.Value = Me.lstEmployee.Column(4, i)
        Me.Emp4.Value = Me.lstEmployee.Column(1, i)
        Me.Emp5.Value = Me.lstEmployee.Column(12, i)
        Me.Emp6.Value = Me.lstEmployee.Column(8, i)
        Me.Emp7.Value = Me.lstEmployee.Column(7, i)
        Me.Emp8.Value = Me.lstEmployee.Column(18, i)
        Me.Emp9.Value = Me.lstEmployee.Column(14, i)
        Me.Emp10.Value = Me.lstEmployee.Column(16, i)
        Me.Emp11.Value = Me.lstEmployee.Column(19, i)
        Me.Emp12.Value = Me.lstEmployee.Column(21, i)
        Me.Emp13.Value = Me.lstEmployee.Column(20, i)
        Me.Emp14.Value = Me.lstEmployee.Column(23, i)
        Me.Emp15.Value = Me.lstEmployee.Column(22, i)
        Me.Emp16.Value = Me.lstEmployee.Column(24, i)
        Me.Emp17.Value = Me.lstEmployee.Column(11, i)
        Me.Emp18.Value = Me.lstEmployee.Column(25, i)
        Me.Emp19.Value = Me.lstEmployee.Column(26, i)
        Me.Emp20.Value = Me.lstEmployee.Column(27, i)
        Me.Emp21.Value = Me.lstEmployee.Column(28, i)
        Me.Emp22.Value = Me.lstEmployee.Column(29, i)
        Me.Emp23.Value = Me.lstEmployee.Column(30, i)
        Me.Emp24.Value = Me.lstEmployee.Column(31, i)
        Me.Emp25.Value = Me.lstEmployee.Column(32, i)
        Me.Emp26.Value = Me.lstEmployee.Column(33, i)
        Me.Emp27.Value = Me.lstEmployee.Column(2, i)        ' returns output as date to text box
        Me.Emp28.Value = Me.lstEmployee.Column(5, i)        ' returns output as date to text box
        Me.Emp29.Value = Me.lstEmployee.Column(6, i)
        Me.Emp30.Value = Me.lstEmployee.Column(13, i)
        Me.Emp31.Value = Me.lstEmployee.Column(17, i)
    On Error GoTo 0

End Sub
 
Upvote 0
Hi,
untested but see if this update to your code resolves the issue

VBA Code: 
Private Sub lstEmployee_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i           As Long, a As Long
    Dim data        As Variant
    
    On Error Resume Next

    i = Me.lstEmployee.ListIndex
    
    For a = 1 To 31
        data = Me.lstEmployee.Column(Choose(a, 0, 3, 4, 1, 12, 8, 7, 18, 14, 16, 19, 21, 20, 23, 22, 24, _
                                            11, 25, 26, 27, 28, 29, 30, 31, 32, 33, 2, 5, 6, 13, 17), i)
        Select Case a
            Case 27, 28
                'returns output as date to text box
                data = Format(DateValue(data), "mm/dd/yyyy")
        End Select
        
        Me.Controls("Emp" & a).Value = data
    Next a
    
    On Error GoTo 0
    
End Sub

Dave
 
Upvote 0
VBA Code: 
Sub RefreshDates()
   Dim D As Variant
    
   D = Me.Emp27.Value
   If VBA.IsNumeric(D) Then Me.Emp27.Value = Format(D, "mm/dd/yy")

   D = Me.Emp28.Value
   If VBA.IsNumeric(D) Then Me.Emp28.Value = Format(D, "mm/dd/yy")
End Sub


See if the above works if you put it at the end of your event code, i.e.

VBA Code: 
        Me.Emp26.Value = Me.lstEmployee.Column(33, i)
        Me.Emp27.Value = Me.lstEmployee.Column(2, i)        ' returns output as date to text box
        Me.Emp28.Value = Me.lstEmployee.Column(5, i)        ' returns output as date to text box
        Me.Emp29.Value = Me.lstEmployee.Column(6, i)
        Me.Emp30.Value = Me.lstEmployee.Column(13, i)
        Me.Emp31.Value = Me.lstEmployee.Column(17, i)
    On Error GoTo 0

     RefreshDates

End Sub
 
Upvote 0
Solution
rlv01 said:
VBA Code: 
Sub RefreshDates()
   Dim D As Variant
   
   D = Me.Emp27.Value
   If VBA.IsNumeric(D) Then Me.Emp27.Value = Format(D, "mm/dd/yy")

   D = Me.Emp28.Value
   If VBA.IsNumeric(D) Then Me.Emp28.Value = Format(D, "mm/dd/yy")
End Sub


See if the above works if you put it at the end of your event code, i.e.

VBA Code: 
        Me.Emp26.Value = Me.lstEmployee.Column(33, i)
        Me.Emp27.Value = Me.lstEmployee.Column(2, i)        ' returns output as date to text box
        Me.Emp28.Value = Me.lstEmployee.Column(5, i)        ' returns output as date to text box
        Me.Emp29.Value = Me.lstEmployee.Column(6, i)
        Me.Emp30.Value = Me.lstEmployee.Column(13, i)
        Me.Emp31.Value = Me.lstEmployee.Column(17, i)
    On Error GoTo 0

     RefreshDates

End Sub
Click to expand...
This is a beautiful thing. Thank you so much, this did the trick.
 
Upvote 0
You must log in or register to reply here.

Similar threads

WildBurrow
  • Solved
Date Validation Error
Replies
8
Views
83
WildBurrow
WildBurrow
S
  • Solved
Use change event to change the format of a date
Replies
2
Views
146
still learning
S
AndyTampa
  • Solved
InputBox to ask for date and only accept standard date formats
2 3
Replies
27
Views
1K
AndyTampa
AndyTampa
H
  • Question
Macro to colour code tab based on date on that tab
Replies
3
Views
289
Trebor76
T
R
  • Solved
Not Date Formats Converting
Replies
3
Views
457
Joe4
Joe4

Forum statistics

Threads
1,214,584
Messages
6,120,387
Members
448,957
Latest member
Hat4Life

Share this page

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
Back
Top