How to change regional setting short date as dd/mm/yyyy through vba code

Question

In India we are using the date format as dd/mm/yyyy (United Kingdom). By default short date is mm/dd/yyyy (United State). How to change the short date format though VBA code in ms excel.

Kindly help me.

Dhandapani

Answer

Dhandapani,

Insert a new module and paste in this code:

Private Const LOCALE_SSHORTDATE = &H1F Private Const WM_SETTINGCHANGE = &H1A

Private Const HWND_BROADCAST = &HFFFF&

Private Declare Function SetLocaleInfo Lib "kernel32" Alias _

         "SetLocaleInfoA" ( _
            ByVal Locale As Long, _
            ByVal LCType As Long, _
            ByVal lpLCData As String) As Boolean
     

Private Declare Function PostMessage Lib "user32" Alias _

         "PostMessageA" ( _
          ByVal hwnd As Long, _
          ByVal wMsg As Long, _
          ByVal wParam As Long, _
          ByVal lParam As Long) As Long
    

Private Declare Function GetSystemDefaultLCID Lib "kernel32" _

          () As Long

Sub chgDate()

Dim dwLCID As Long
Dim myDate As String
        
dwLCID = GetSystemDefaultLCID()
        
        
If SetLocaleInfo(dwLCID, LOCALE_SSHORTDATE, "dd/MM/yyyy") _
     = False Then
     MsgBox "Failed"
     Exit Sub
End If
PostMessage HWND_BROADCAST, WM_SETTINGCHANGE, 0, 0
        

End Sub

'---------- change the "dd/MM/yyyy" to the format you want.

the code was tested and worked for me.