If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Todays Date shown in VBA Forms in Excel

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Location: Stalybridge, Manchester
Posts: 273
Todays Date shown in VBA Forms in Excel

Hi, I have designed a VBA form in Excel and when the user clicks on the add new record button I want it to populate another text box with todays date.

This below is my code I have used and it populates the textbox with a date but its an american style date. this is no good to me, how can I change it around for the british way?

Private Sub cmdAdd_Click()
lngLastRow = findLastRow(Sheets("ToSend").Range("A2"), "") + 1
Me.cmdAdd.Enabled = False
Me.txtInputDate = Now()
Me.txtInputBy = fOSUserName
End Sub

I then tried this that had used in Access before and all it did was put it the correct way but show as text numbers.

Private Sub cmdAdd_Click()
lngLastRow = findLastRow(Sheets("ToSend").Range("A2"), "") + 1
Me.cmdAdd.Enabled = False
Me.txtInputDate = Timer.timeSince("30/12/1899", VBA.DateTime.DateValue(VBA.DateTime.DateSerial(Yea r(usrFrmCustInput.CreateDate), Month(usrFrmCustInput.CreateDate), Day(usrFrmCustInput.CreateDate))))
Me.txtInputBy = fOSUserName
End Sub

Module Timer
Function timeSince(strDateFrom As String, strDateTo As String) As Double
If VBA.IsDate(strDateFrom) And VBA.IsDate(strDateTo) Then
Dim lngDays As Long
lngDays = VBA.DateDiff("d", strDateFrom, strDateTo, vbSunday)
timeSince = lngDays
Else
timeSince = 0
End If
End Function
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Dec 2004
Posts: 37
Format?

Try

Me.txtInputDate = Format(Now(),"dd\mm\yy")

Someone may come along wit a more elegant solution, but this should work

Mike
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Location: Stalybridge, Manchester
Posts: 273
Thanks Mike, I tried that and this is the result in the textbox 10m1y0810
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Dec 2004
Posts: 37
Teach me to cut and paste rather than re-type

Sorry,

should be

Me.txtInputDate = Format(Now(),"dd/mm/yy")

Change the direction of the slashes

Mike
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Location: Stalybridge, Manchester
Posts: 273
Teach me to read properly too :-)

Thanks, hadn't noticed the slash
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On