Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: A Macro Question

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is more a question of curiousity.

    Why does a macro sometimes reference an old workbook ie(the one it was created in) when the code has been copied to a new workbook. I have a macro that does this everytime I run it, it pops up a file dialog box, looking for its old file path. No where in the macro is a reference to a file path and in the same module there are macros that were copied from the same old workbook that work just fine. I find this quite odd.

    Anyone know what causes this?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not a solid idea....Show the naughty code.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-18 17:43 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The common problem for this is a "manually" created toolbar, that hasn't been attached to the workbook. So, it just keeps looking for the "old" file...

    I always try to create my toolbars on the fly (At opening, and delete them at close)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nate,

    Here is the code in question:

    Sub copy_top()
    '
    For i = 0 To 29
    Range("c6:c17").Select
    Selection.Copy
    Sheets(1 + i).Select
    Range("c6").Select
    ActiveSheet.Paste
    Next
    Sheets(1).Select
    End Sub
    -------------------------------------------
    Sub copy_bottom()
    '
    For i = 0 To 29
    Range("B24:G35").Select
    Selection.Copy
    Sheets(1 + i).Select
    Range("B24").Select
    ActiveSheet.Paste
    Next
    Sheets(1).Select
    End Sub

    both macros reside in the same module and both were copied form the same workbook. But the the copy_top, trys to look for its old spread sheet like a lost puppy.

    Juan, what you said makes since there are several macro tool bars tied to excel it self, from a previous user. What confuses me about this is it doe sit even when I run from VBA editor. Any ideas, this is more of an annoyance than anything else, the macro still does its job, its i just have to hit cancel for every sheet. Not something I want in my final product.

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think your post that Barrie just responded to is pertinent. If you have a link to a cell in another workbook that does not exist in the designated file path in the range c6:c17 you will get a dialog each time you try to paste the bad link.

    Get rid of the bogus links and your troubles are all behind you.

    Hope this is getting warmer...

    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-19 09:47 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nate,

    I think you are right, That makes prefect sense now. And it just hit me how you remove the links. Duh.. If I am right you just clear the contents in the offending cells and save and the links should be gone.

    I will give that a try anyway, seems to be the way to make it work...

Some videos you may like

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
  •