easy macro question

chookers · May 13th, 2002, 12:13 AM

Hello, I have been working on my macro storage problem and I have noticed that the problem I was having of my macro button reassigning itself to the latest document based on the template (i.e. it would reassign itself from "template" to "Document1" and if "Document1" was ever deleted, the macro couldn't find the code and wouldn't work) and not staying with the template itself, does not happen if the macro is stored in Personal.xls (I had it in This workbook and I also tried all open workbooks with the same result)

I would love to understand why this is so, but barring that I would love to know if Personal.xls is an appropriate place to put macros that I need to have be used on other people's computers. I have to email the template out to a few different people, should this work?

Thanks for any clues! Excel help is hopeless on this subject I'm sorry to say.

Cheers!

Tom Schreiner · May 13th, 2002, 12:24 AM

Your personal macro workbook will remain on your computer.
You may want to import the module(s) you need into each new workbook created from your template.
Tom

chookers · May 13th, 2002, 12:37 AM

Testing to see if this posts, my last one didn't.

chookers · May 13th, 2002, 12:51 AM

Jeez I have lost 3 messages so far! Well let me try this one more time...

I was afraid someone was going to tell me that. The trouble is that when I assign it to "This workbook" or "all open workbooks" The macro button doesn't STAY assigned to the template, it reassigns itself to the last document I used based on the template. So I can send out this worksheet template with the toolbar, but as soon as a party on the other end goes to press the macro button is says it can't find "Document1" or whatever I've called it, which is here on my computer. There must be a way to make it stay with the template so when someone uses it it doesn't have to be reassigned to the template everytime? Sweet Jesus I have looked everywhere for a solution to this and can't find one!!!

Any other ideas?

Tom Schreiner · May 13th, 2002, 01:36 AM

Hi Snookers...
I just created a template with a forms button and saved a copy as an Excel Workbook.
The macro followed and changed it's reference/name automatically...
The original template was unaffected...
We're missing something here?
The only thing I can come up with is you are using a macro from another workbook in your original template.
Open your original template, right click on your button, choose assign macro. Make sure the macro assigned is in your current template. The filename should precede the macro name. Ex. TemplateTemp1!Button3_Click
The filename is TemplateTemp1
The macro name is Button3_Click
We'll figure it out.
Tom

[ This Message was edited by: TsTom on 2002-05-12 20:37 ]

chookers · May 13th, 2002, 02:08 AM

Oh my Goodness, thank yo ufor giving me some hope, even though I still am not understanding where my problem is. Here's what I did to double check everything.

The Template is named SOSE.xlt
The Macro is named SOSECutPaste

I've gone into explorer to open the template and right clicked on the button. Here's what it says:

SOSE.xlt!SoseCutPaste -- Really good, right?

Next, Opened Excel>File>New>SOSE Template

Used the workbook, no worries, saved it to the desktop as TEST, and closed Excel.

Went back to Excel and File>New>SOSE template
Right clicked on the macro button (it's on a toolbar that opens and closes with the document - not on a form)and now it's assigned to

C:WindowsDesktopTEST.xls!SOSECutPaste

Same thing (obviously) if I go back to explorer and open the template. It's no longer SOSE.xlt!SOSECutPaste but as above.

Now I can deal with this here cuz I know what to do, but I know it isn't right. Now let's say I delete the TEST.xls document from the desktop and try to use my SOSE template with the macro -- forget it, it's looking for TEST to find the macro and I've deleted it, at far is it thinks, the macro is gone.

That the h*** is going on? What can I do to trouble shoot this? Do you have any idea where the problem may be?

Let me know if any ideas I can try. So far everything has failed.

Thank you, Lauren

Tom Schreiner · May 13th, 2002, 02:13 AM

Someone here will figure this out.
If you wish, email the file and I will try to duplicate the problem on my sys.
Tom

Excel_Create@Earthlink.net

Tom Schreiner · May 13th, 2002, 02:21 AM

I followed your steps to the letter...
Created template1.xlt and saved it in the templates folder...
Opened Excel, File, New, Template1
Saved as an Excel Workbook Test.xls to my desktop.
The macro followed the newly created workbook and when I reopened the template, the macro name was unaffected??
Eeeeeeeeeeeeek!
We'll figure it out!!!!
Tom

Tom Schreiner · May 13th, 2002, 02:46 AM

I have your file and it is doing the same thing here as on your comp...
I'm beginning to waiver, but I'll say it again, We'll figure it out!
Tom

chookers · May 13th, 2002, 02:57 AM

Ha! I knew I wasn't insane. Maybe what I will do is rewrite the code from scratch and see if that makes any difference. I have to leave work in about 1/2 hour so I may not have time before I go but I will try a test macro from scratch, a short easy one, and see if that works ok.

Thanks, Mr T!

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

May 13th, 2002, 12:13 AM	#1
chookers Join Date: Mar 2002 Posts: 115	Hello, I have been working on my macro storage problem and I have noticed that the problem I was having of my macro button reassigning itself to the latest document based on the template (i.e. it would reassign itself from "template" to "Document1" and if "Document1" was ever deleted, the macro couldn't find the code and wouldn't work) and not staying with the template itself, does not happen if the macro is stored in Personal.xls (I had it in This workbook and I also tried all open workbooks with the same result) I would love to understand why this is so, but barring that I would love to know if Personal.xls is an appropriate place to put macros that I need to have be used on other people's computers. I have to email the template out to a few different people, should this work? Thanks for any clues! Excel help is hopeless on this subject I'm sorry to say. Cheers!

May 13th, 2002, 12:24 AM	#2
Tom Schreiner Join Date: Mar 2002 Posts: 6,680	Your personal macro workbook will remain on your computer. You may want to import the module(s) you need into each new workbook created from your template. Tom

May 13th, 2002, 12:37 AM	#3
chookers Join Date: Mar 2002 Posts: 115	Testing to see if this posts, my last one didn't.

May 13th, 2002, 12:51 AM	#4
chookers Join Date: Mar 2002 Posts: 115	Jeez I have lost 3 messages so far! Well let me try this one more time... I was afraid someone was going to tell me that. The trouble is that when I assign it to "This workbook" or "all open workbooks" The macro button doesn't STAY assigned to the template, it reassigns itself to the last document I used based on the template. So I can send out this worksheet template with the toolbar, but as soon as a party on the other end goes to press the macro button is says it can't find "Document1" or whatever I've called it, which is here on my computer. There must be a way to make it stay with the template so when someone uses it it doesn't have to be reassigned to the template everytime? Sweet Jesus I have looked everywhere for a solution to this and can't find one!!! Any other ideas?

May 13th, 2002, 01:36 AM	#5
Tom Schreiner Join Date: Mar 2002 Posts: 6,680	Hi Snookers... I just created a template with a forms button and saved a copy as an Excel Workbook. The macro followed and changed it's reference/name automatically... The original template was unaffected... We're missing something here? The only thing I can come up with is you are using a macro from another workbook in your original template. Open your original template, right click on your button, choose assign macro. Make sure the macro assigned is in your current template. The filename should precede the macro name. Ex. TemplateTemp1!Button3_Click The filename is TemplateTemp1 The macro name is Button3_Click We'll figure it out. Tom [ This Message was edited by: TsTom on 2002-05-12 20:37 ]

May 13th, 2002, 02:08 AM	#6
chookers Join Date: Mar 2002 Posts: 115	Oh my Goodness, thank yo ufor giving me some hope, even though I still am not understanding where my problem is. Here's what I did to double check everything. The Template is named SOSE.xlt The Macro is named SOSECutPaste I've gone into explorer to open the template and right clicked on the button. Here's what it says: SOSE.xlt!SoseCutPaste -- Really good, right? Next, Opened Excel>File>New>SOSE Template Used the workbook, no worries, saved it to the desktop as TEST, and closed Excel. Went back to Excel and File>New>SOSE template Right clicked on the macro button (it's on a toolbar that opens and closes with the document - not on a form)and now it's assigned to C:WindowsDesktopTEST.xls!SOSECutPaste Same thing (obviously) if I go back to explorer and open the template. It's no longer SOSE.xlt!SOSECutPaste but as above. Now I can deal with this here cuz I know what to do, but I know it isn't right. Now let's say I delete the TEST.xls document from the desktop and try to use my SOSE template with the macro -- forget it, it's looking for TEST to find the macro and I've deleted it, at far is it thinks, the macro is gone. That the h*** is going on? What can I do to trouble shoot this? Do you have any idea where the problem may be? Let me know if any ideas I can try. So far everything has failed. Thank you, Lauren

May 13th, 2002, 02:13 AM	#7
Tom Schreiner Join Date: Mar 2002 Posts: 6,680	Someone here will figure this out. If you wish, email the file and I will try to duplicate the problem on my sys. Tom Excel_Create@Earthlink.net

May 13th, 2002, 02:21 AM	#8
Tom Schreiner Join Date: Mar 2002 Posts: 6,680	I followed your steps to the letter... Created template1.xlt and saved it in the templates folder... Opened Excel, File, New, Template1 Saved as an Excel Workbook Test.xls to my desktop. The macro followed the newly created workbook and when I reopened the template, the macro name was unaffected?? Eeeeeeeeeeeeek! We'll figure it out!!!! Tom

May 13th, 2002, 02:46 AM	#9
Tom Schreiner Join Date: Mar 2002 Posts: 6,680	I have your file and it is doing the same thing here as on your comp... I'm beginning to waiver, but I'll say it again, We'll figure it out! Tom

May 13th, 2002, 02:57 AM	#10
chookers Join Date: Mar 2002 Posts: 115	Ha! I knew I wasn't insane. Maybe what I will do is rewrite the code from scratch and see if that makes any difference. I have to leave work in about 1/2 hour so I may not have time before I go but I will try a test macro from scratch, a short easy one, and see if that works ok. Thanks, Mr T!