Macro works correctly from VBE but not from button

**tone640** · 03-14-2012, 01:04 PM

In the attached, the macro works correctly when run from the VBE, but when running from a button, the dates end up in American format and some of the dates are unaffected.

Any idea why this is happening?

Thanks in advance.

**Mordred** · 03-14-2012, 01:15 PM

What exactly are you trying to accomplish?

**JBeaucaire** · 03-14-2012, 01:38 PM

The problem is you're not taking into account the fact that the time strings in V:X are in the wrong format to start with. To turn them from strings into Date values you have to fix the data, too. Try this, edit the date format to the one you want:

Please Login or Register  to view this content.

**tone640** · 03-15-2012, 06:44 AM

I was trying to avoid several lines of code by using one - mine works when it is run from the VBE.

Please Login or Register  to view this content.

I still don't understand why this same line of code doesn't work from a button, but many thanks for providing a solution that works - it's much appreciated.

**JBeaucaire** · 03-15-2012, 10:45 AM

I tried to explain it. Your macro DOES apply the command to the range of cells as given, it just doesn't "work". You haven't dealt with the fact that the original strings are simply NOT in a format that fixes the dates correctly.

When I run your original macro many of the cells DO change to true dates, but they were changing to the wrong dates. So in truth, it wasn't working on ANY of the cells. The ones it converted were converted incorrectly, and other cells it simply didn't work on at all.

My macro "extracts" the year, month and day values from the string, then the time value, then reconstructs it all into an Excel timestamp you can then format to your liking.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.