Cell date formats changed by macro aren't sticky
Cell date formats changed by macro aren't sticky
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Cell date formats changed by macro aren't sticky

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Thousand Oaks, CA
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Thousand Oaks, CA
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Thousand Oaks, CA
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ???


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com