A Macro Question

kluitna · Mar 18th, 2002, 06:32 PM

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?

NateO · Mar 18th, 2002, 06:37 PM

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

_________________
Cheers, NateO

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

Juan Pablo González · Mar 18th, 2002, 07:43 PM

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)

kluitna · Mar 19th, 2002, 10:24 AM

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.

NateO · Mar 19th, 2002, 10:45 AM

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 ]

kluitna · Mar 19th, 2002, 11:09 AM

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

Mar 18th, 2002, 07:43 PM	#3
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 06:32 PM	#1
kluitna Board Regular Join Date: Mar 2002 Posts: 75	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?

Mar 18th, 2002, 06:37 PM	#2
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Not a solid idea....Show the naughty code. _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-03-18 17:43 ]

Mar 19th, 2002, 10:24 AM	#4
kluitna Board Regular Join Date: Mar 2002 Posts: 75	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.

Mar 19th, 2002, 10:45 AM	#5
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 ]

Mar 19th, 2002, 11:09 AM	#6
kluitna Board Regular Join Date: Mar 2002 Posts: 75	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...