Cell date formats changed by macro aren't sticky

[dedawson]

I have a workbook with numerous sheets whose date cells are formatted m/d/yy. When I manually copy these sheets to a new workbook by right clicking the sheet's tabs and then selecting (new book) and Make a Copy, all is well. The dates in the new workbook remain formatted as m/d/yy. If I perform this same action via a VBA macro, the dates in the new workbook are formatted as m/d/yyyy.

But wait, it gets worse. I can manually change the m/d/yyyy formatting of the new workbook to m/d/yy. If I do this with a macro, however, the changes aren't maintained. In fact, if one steps through the macro and watches the screen, when the line of code to set the format of the date to m/d/yy is executed, the change in fact occurs. When the next line of code executes, the cells just changed immediately revert to m/d/yyyy. This also occurs when attempting to change the date format on the axes of charts. What in the world is going on?!

[ This Message was edited by: dedawson on 2002-02-18 09:20 ]

[ This Message was edited by: dedawson on 2002-02-18 09:20 ]

[Damon Ostrander]

Hi David,

I just tried this and couldn't duplicate the problem with Excel 97. What version are you using? Perhaps it would help to post the VBA code you are using. I assumed you are setting the cell date formatting using the cell's NumberFormat property.

[dedawson]

Hi back to you Damon,

I'm running Excel 97 SR-2 on NT 4.0

Here's the code (I hope it unwraps at your end):

Windows("CPCI Test Proc Gen metrics B1 15 procs rev1.xls").Activate
Sheets(Array("OverallCharts", "CVSV", "CPISPI", "cscitestunsort", _
"CSCITestProcDevreal")).Select
Sheets(Array("OverallCharts", "CVSV", "CPISPI", "cscitestunsort", _
"CSCITestProcDevreal")).Copy Before:=Workbooks("Book1").Sheets(1)


Sheets(Array("OverallCharts", "CVSV", "CPISPI", "cscitestunsort", _
"CSCITestProcDevreal")).Select
Sheets(Array("OverallCharts", "CVSV", "CPISPI", "cscitestunsort", _
"CSCITestProcDevreal")).Copy
Sheets("cscitestunsort").Select
Sheets("cscitestunsort").Name = "CSCITestProcOverall"
Sheets("CSCITestProcDevreal").Select
Sheets("CSCITestProcDevreal").Name = "CSCITestProcDetails"
Sheets("OverallCharts").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.NumberFormat = "0.0%"
ActiveChart.SeriesCollection(5).DataLabels.Select
Selection.NumberFormat = "0.0%"
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.NumberFormat = "0.0%"
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "0%"
Range("OverallCharts_Dates_To_Format").Select
Selection.NumberFormat = "m/d/yy"

Sheets("CVSV").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "m/d/yy"
Range("CVSV_Dates_To_Format").Select
Selection.NumberFormat = "m/d/yy"

Sheets("CPISPI").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "m/d/yy"
Range("CPISPI_Dates_To_Format").Select
Selection.NumberFormat = "m/d/yy"

Its these bottom two chunks that are having the problem.

One other item, this occurs on two different machines that I've run the code on.

If this still doesn't make sense and you're still interested in helping me sort it out, send me a number and I'll give you a call on my dime.

Thanks for your interest and help.

david

[ This Message was edited by: dedawson on 2002-02-19 14:44 ]

[ This Message was edited by: dedawson on 2002-02-19 15:16 ]

[Jack in the UK]

Hi

Try after the copy - code out so that the pasted data is selected and then paste as number format in simple code to text and d/m/y or as required.. This way Excel should do as you want..

HTH
Rdgs
==========
Jack

[dedawson]

Hi Jack,

Kind of late there isn't it?

I'm not certain I follow what you're telling me. What I did try at one point however, was that after the copy I had code that formatted the cells in question as Number, and then reformatted them to "m/d/yy". No help.

Is this what you were suggesting?

Thanks, but don't lose any sleep tonight.

david

[Jack in the UK]

Hi --

Yep very late mate 12PM ish i think all my post are very late- i sleep dreadfully i dream of Excel and sometimes Excel for a change!

Yes you correct, i just copy and with selection format as i want that way Excel is FORCED to do as i like .. same as :

="Jack says today is" & NOW() will fail needs to be formatted to text on the NOW() part

Copy and paste is always a chore..

Im tyred ////// but a pal Dave Hawley http://www.ozgrid.com has loads on goodies VBA CopySelect and destinations Paste in hard codes in one lines.. try having a look.. VBA Section and also see a little bit of everthing.

Say hi for me to Dave in Western Austraila if you see him.

HTH
Rdgs
==========
Jack ???