DataForms and dates
 

After searching the newsgroup I think mine is an old problem with a
new twist?

I've set up a system in Excel for secretaries to process referrals to
a child mental health service (lots more involved in it), using VBA,
which involves many different dataforms. Until this week there was
never any problem with the date formatting but now the dates appear in
the dataform in mm/dd/yy format rather than UK dd/mm/yy. Quite apart
from messing up data entry big time, it would appear to have caused a
problem with some date functions for a number of variables which are
now coming up with a type mismatch error? (at least nothing else in my
code has been changed that might cause this).

Given that the Trust's IT department cannot help with this sort of
thing, I searched online and found this forum and a search revealed
that that the dataform apparently reverted to American date format
when called from code. However Activesheet.ShowDataForm has worked
perfectly for me for 6 years! And I know of no other way to activate
it in Excel 95 as per the code provided for 97 :
(Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=860,
recursive:=True).Execute) as a solution for that version of Excel. I
have not got the time to set the system up in XL97.

The only ‘explanation' I can see as to why it has suddenly stopped
working is that at the end of last week the file containing the
database and the code was copied to a laptop, updated on the laptop
(in Excel 97) and then transferred back to the desktop PC into Excel
95. Checking with the person who updated it, the dates displayed in
the dataforms on the laptop were apparently in mm/dd/yy format! But
how could this have resulted in the desktop PC now having the same
problem? And is there some way I can reset it to work?

The first thing I did was to check the Regional Settings in Control
Panel which were (and are) correct for both long and short dates.
Then, digging everywhere I could think of, I found in the file in
Tools Options (Excel 95) an International section which was set at
Current Settings with Language/Country set at English/United States
(greyed out) and the dates in the wrong format. If I select the
Default Settings option button, it changes to English/United Kingdom
and the dates revert to what they should be in the dialog box.
HOWEVER, when I clicked OK and went back in to have a look, the
setting was back to Current and US format!! No matter how many times I
try it, this is what happens. Even if I click on another tab and then
back to Module General, it's reverted to the American format!

A new workbook opens perfectly with the Default setting English/UK and
correctly formatted dates. I thought therefore that I would copy the
worksheets and all the module and dialog sheets into a new book as
this might solve the problem. When I checked, the date format was
indeed correct in Tools Options but when I looked in the Menu Editor,
my custom Menu Bar with its many submenus is missing! The amount of
time it would take to recreate them would be enormous, so this doesn't
look like much of a solution - why the menus do not carry over I have
no idea since I'm copying absolutely everything form the old
workbook!?

Can anyone help? The team depends on the system, which has worked well
for years, as a clinician have precious little time to work on it (set
it up mostly in my own time in the evenings) and on this occasion have
no idea how to solve the problem.


Sorry this post is so long but I would really, really appreciate any
help to sort this one out!


Thanks.

Colin Elliott