I have the following in a VBA add-in:

Rows(1).Insert
Range("A1").Value = "H"
Range("B1").Value = MemoNumber
Range("C1").Value = Date
Range("C1").NumberFormat = "m/d/yyyy"
Range("C1").NumberFormat = "@"

I use the last three lines to obtain a date in the format m/d/yyyy for
input to another process. Problem is, this doesn't always
work. I use the exact same code in various places in the
add-in. Sometimes it works, sometimes I don't get the format
I expect. I have just inserted the row - shouldn't this work
the same every time?

Kevin

--
Message posted via http://www.officekb.com

First, I'd drop that last line:
Range("C1").NumberFormat = "@"

It can't be helping.

=========


You may have been hit by a coincidence.

When you do Format|cells|Number tab and look at that cell's format, I'm betting
you see something like:

*3/14/2001

That asterisk means that you chose a short date format that is picked up from
the Windows Regional settings (Date tab).

xl2002+ is more honest with the way it deals with dates.

At the bottom of that dialog (xl2002):

Date formats display date and time serial numbers as date values. Except for
items that have an asterisk (*), applied formats do not switch date orders with
the operating system.

Since yours is marked with an asterisk, your date will switch formats with the
setting in the OS.

=========

My windows short date format is mm/dd/yyyy. So I see 06/29/2005 (with the
leading 0s) when I run your code.

=========

Maybe a slight change to the format would work:
Range("C1").NumberFormat = "m/d/yyyy "
(an additional space)



"Kdub via OfficeKB.com" wrote:

I have the following in a VBA add-in:

Rows(1).Insert
Range("A1").Value = "H"
Range("B1").Value = MemoNumber
Range("C1").Value = Date
Range("C1").NumberFormat = "m/d/yyyy"
Range("C1").NumberFormat = "@"

I use the last three lines to obtain a date in the format m/d/yyyy for
input to another process. Problem is, this doesn't always
work. I use the exact same code in various places in the
add-in. Sometimes it works, sometimes I don't get the format
I expect. I have just inserted the row - shouldn't this work
the same every time?

Kevin

--
Message posted via http://www.officekb.com

--

Dave Peterson