Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste Special macro (2000)

    I have a toolbar button that runs the following simple macro:
    <code>
    Sub PasteSpecialText()
    ActiveSheet.PasteSpecial Format:="Text"
    End Sub
    </code>
    This can work in funny ways. If I use it to paste in the date 25/11/2004 into a cell custom formatted as d/mm/yy then it appears as shown. If I do exactly the same thing using Paste Special (Text) from a menu item, it appears as expected - 25/11/04. I can correct this behaviour by using the macro, then putting the cursor in the Formula Bar and pressing Enter.

    Any ideas on how to rectify this?

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Special macro (2000)

    You have run into one of the situations where a VBA instruction does not do exactly the same as its interactive equivalent. When you select Edit | Paste Special... and select the Text option (if applicable), Excel applies extra intelligence: it recognizes that you are pasting a date. If you run ActiveSheet.PasteSpecial Format:="Text", VBA pastes the clipboard contents as text, without recognizing it as a date. Hence the number format has no effect.

    It would be a lot of work to duplicate the internal Excel programming.

    Note: ActiveSheet.PasteSpecial Format:="Text" seems to be an excellent way to make Excel crash!

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Special macro (2000)

    OK, I'll concede that I'm up against a limitation here <img src=/S/sad.gif border=0 alt=sad width=15 height=15>.

    I've used this code in lots of places and situations, but have never experienced a crash as a result of it AFAIK. Why do you say this Hans? Is it from experience, or is there something in such code that is inherently risky?

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Special macro (2000)

    While researching your question, Excel froze about half the times I ran the macro... No idea why. I could still get out using the Task Manager.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Special macro (2000)

    The way I use it is to copy from a "text source" (like a web page) and paste into just a single cell selection. I guess it could explode if used for situations for which it's not intended. For instance, it generates a runtime error if I try to paste a copied XL cell... can't get it to crash it though.

    Alan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Special macro (2000)

    Never mind, these things happen sometimes...

Posting Permissions

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