textbox date

[bearbubble]

when entering a date from a textbox in a excel vb userform
the date get turned around (dd mm yy entered but mm dd yy ends up in the cell)
the cell format is set for dd mm yy
excel is set to Uk
how can i stop this

 

[rdeleon]

I do not think there is a way to create a date format for a textbox without code (If you find a way let me know).

It sounds like there is some code that is translating the date from dd mm yy to mm dd yy when the user enters the date. Is there a Change or AfterUpdate event for the textbox?

Rene'

 

[SkipVought]

hi bearbubble,

First, it's important to know what Excel Date/Time values are. Dates are whole numbers: 1 is 1/1/1900 and today is 37884

Then you assign the TEXT VALUE from your TextBox, for instance 30 9 03, it assigns "30 9 03" to the box REGARDLESS of the date formatting for that cell.

What you need to do is CONVERT "30 9 03" to an Excel Date. This is done via the DateSerial function. DateSerial has Year, Month, Day as integer arguments. So what you need to do is parse "30 9 03" and put those parts in the DateSerial function like this

TargetCell.Value = DateSerial(2003, 9, 30)

so you code for assigning the textbox text to the cell might look like this...

    Dim iYr As Integer, iMon As Integer, iDay As Integer
    n = 1
    With TextBox1
        For i = 1 To 2
          n = InStr(n, .Text, " ")
          Select Case i
          Case 1
            iDay = Left(.Text, n - 1)
            n = n + 1
            n1 = n
          Case 2
            iMon = Mid(.Text, n1, n - n1)
          End Select
        Next
        iYr = Right(.Text, Len(.Text) - n)
    End With
    If iYr > 50 Then
        iYr = iYr + 1900
    Else
        iYr = iYr + 2000
    End If
    ActiveCell.Value = DateSerial(iYr, iMon, iDay)

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Grayfox081877]

i think the problem is the default date of your system setting is in mm dd yy format. You can check your regional options in control panel.

If you want to code it... you can look for some API that will trigger upon loading of your Excel file.

You can refer to this link.

http://support.microsoft.com/defaul...port/kb/articles/Q177/1/46.asp&NoWebContent=1

Gray

 

[Grayfox081877]

Sorry that is for foxpro. Here's another solution in VB, hope this one can help.

I had thought that the answer would be shrouded in obscurity and mystery, and require the use of either the registry or the initialization file functions in the Win32 API, along with some spying tools to see from where Windows was retrieving its regional settings. As it turns out, though, that's really unnecessary; as Visual Basic programmers, we just don't pay much attention to regional settings because Visual Basic -- for better or for worse -- handles them for us, and so their operation is somewhat mysterious to us.




Public Const LOCALE_SSHORTDATE = &H1F



Public Declare Function GetSystemDefaultLCID _

Lib "kernel32" () As Long



Public Declare Function SetLocaleInfo Lib _

"kernel32" Alias "SetLocaleInfoA" ( _

ByVal Locale As Long, _

ByVal LCType As Long, _

ByVal lpLCData As String) As Boolean

/*
'Note that you want to use the ByVal keyword 'in passing lpLCData to the function, since you want to pass a pointer to a null-terminated C string; if you pass the argument by reference, you end up passing a pointer to a Visual Basic string, which will have rather unfortunate consequences.

Once you've figured out which Win32 API functions to call, changing the setting is simplicity itself:
*/


Private Sub Main()



Dim lngLocale As Long



lngLocale = GetSystemDefaultLCID()



If SetLocaleInfo(lngLocale, _

LOCALE_SSHORTDATE, _

"MM/dd/yyyy&quot = _

False Then

' Handle error, possibly by writing it

' to a server error log

End If



End Sub




But in fact, a single Win32 function, SetLocaleInfo, can be used to set a wide range of regional settings. On success, the function returns a non-zero value; otherwise, it returns zero (or False).