Solved

Script to Change Format

Posted on 2012-12-20
13
443 Views
Last Modified: 2012-12-21
Hi Experts,

Is it possible to write a VBA Code to check if the system's Short Date format is mm/dd/yyyy, and change it if isnt?

Thank you
0
Comment
Question by:APD_Toronto
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 75
ID: 38710662
Just a comment - as opposed to a solution.
Seems doing so could really piss off a user(s) who wants that Format.

Instead, why not hard code the format you want to display in a Control's Format property and/or in code.  This will override the Windows whatever format.

This is what I do ... as I prefer mm-dd-yyyy

mx
0
 

Author Comment

by:APD_Toronto
ID: 38710692
pissing off the user would be a bonus :)

the application is huge, and dates are used in literaly 100s of places, so my solution above is most economical.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38710981
<the application is huge, and dates are used in literaly 100s of places, so my solution above is most economical. >
By "100s of places", ...do you mean geographic locations?
(different countries)
Because changing this for all computers in all countries may cause problems...
There may be some utilities installed on some machines that need a different date setting...

Is there a specific issue you are having...?
Like MX, I am just a bit wary of just blowing though and making this change for all computers...
...When a change to the date format in Access may be all that is really needed...


In any event, a link to investigate:
http://en.allexperts.com/q/Excel-1059/2010/9/change-Regional-Setting-short.htm

JeffCoachman
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:APD_Toronto
ID: 38711418
Because changing this for all computers in all countries may cause problems...

WOW.  It's one office, 300 sqft with 7-10 PCs.  

What i meant by 100 places was, something like 30 forms + 50 sub routines + 20 queries = 100 places
0
 
LVL 75
ID: 38711435
Just curious ... does the Format really matter since it has not bearing on how the date is actually stored in a table internally ?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38711813
MX,

I'll leave this to you...
I gotta get started on the Holiday stuff...
;-)

Jeff
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 38711937
Hi, I guess it's up to you whether you want to make your system more robust, or just force the change of the "short date format" by using this code:

Set objShell = CreateObject("WScript.Shell")
sDateFormat = objShell.RegRead("HKCU\Control Panel\International\sShortDate")
If sDateFormat <> "mm/dd/yyyy" Then objShell.RegWrite "HKCU\Control Panel\International\sShortDate", "mm/dd/yyyy", "REG_SZ"

Open in new window


Regards,

Rob.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38712340
You have to deal with possible different Windows settings for the users, as in a properly set up environment, users will not have been granted permission to change the date and time settings.

The method is simple; whenever a date/time is displayed, set the Format property of the textbox to: mm/dd/yyyy

/gustav
0
 
LVL 9

Expert Comment

by:shorvath
ID: 38713461
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
0
 
LVL 75
ID: 38713696
"as in a properly set up environment, users will not have been granted permission to change the date and time settings."
Especially at our LARGE company now that Win7 is being deployed ... total lock down ... barely can write to Registry at all.

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38713719
Yeah, same goes for changing screen resolution - popular in the days of CRT monitors - a big no-no.

/gustav
0
 
LVL 75
ID: 38713754
Well .. users here can manually change screen res of course ... many still use 800x600 on 24" dual monitors!  Can you say"  I cant' see' !
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38713773
Really? I haven't seen that on modern flat screen monitors. We always use the default (optimum) resolution.

/gustav
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question