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
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.
Advertisement