Cell date formats changed by macro aren't sticky

dedawson

New Member
Joined
Feb 17, 2002
Messages
4
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 :) ???
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top