Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links broken when spreadsheet emailed
I created a spreadsheet template that has some VBA code in it, then created
two buttons to call the methods. This way, the person did not have to go to tools, macros and find the one they needed. But when I emailed the spreadsheet to the user, they pressed the toolbar button and it held a reference to my computer instead of the worksheet like I thought. When I originally set it up, I assigned the toolbar button to "ThisWorkbook.MyMacro" and thought this would carry over to another computer without messing up the reference. How can I prevent this from happening so a user will not have to reassign the toolbar buttons each time it is emailed? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links broken when spreadsheet emailed
I don't know where this syntax came from:
"ThisWorkbook.MyMacro" It should be assigned to "'My Workbook Name.xls'!MyMacro" Note that the workbook name is surrounded with single quotes if it contains dashes or spaces. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Wannabe" wrote in message ... I created a spreadsheet template that has some VBA code in it, then created two buttons to call the methods. This way, the person did not have to go to tools, macros and find the one they needed. But when I emailed the spreadsheet to the user, they pressed the toolbar button and it held a reference to my computer instead of the worksheet like I thought. When I originally set it up, I assigned the toolbar button to "ThisWorkbook.MyMacro" and thought this would carry over to another computer without messing up the reference. How can I prevent this from happening so a user will not have to reassign the toolbar buttons each time it is emailed? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links broken when spreadsheet emailed
Hi Wannabe;
Try creating drawing objects and make them buttons instead of using a custom toolbar. Then assign the macros to the drawing objects. Thanks, "Wannabe" wrote: I created a spreadsheet template that has some VBA code in it, then created two buttons to call the methods. This way, the person did not have to go to tools, macros and find the one they needed. But when I emailed the spreadsheet to the user, they pressed the toolbar button and it held a reference to my computer instead of the worksheet like I thought. When I originally set it up, I assigned the toolbar button to "ThisWorkbook.MyMacro" and thought this would carry over to another computer without messing up the reference. How can I prevent this from happening so a user will not have to reassign the toolbar buttons each time it is emailed? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links broken when spreadsheet emailed
If your irregular syntax comes from having the macros on the ThisWorkbook
code module, you should move them to a regular module. Then assign them to the buttons as I described. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... I don't know where this syntax came from: "ThisWorkbook.MyMacro" It should be assigned to "'My Workbook Name.xls'!MyMacro" Note that the workbook name is surrounded with single quotes if it contains dashes or spaces. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Wannabe" wrote in message ... I created a spreadsheet template that has some VBA code in it, then created two buttons to call the methods. This way, the person did not have to go to tools, macros and find the one they needed. But when I emailed the spreadsheet to the user, they pressed the toolbar button and it held a reference to my computer instead of the worksheet like I thought. When I originally set it up, I assigned the toolbar button to "ThisWorkbook.MyMacro" and thought this would carry over to another computer without messing up the reference. How can I prevent this from happening so a user will not have to reassign the toolbar buttons each time it is emailed? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links broken when spreadsheet emailed
That syntax comes from when I choose customize toolbar and then right click
the toolbar button and select assign macro. Then I can choose to use Macros in "This Workbook", "all open workbooks", and the one that has my spreadsheet name. The reason I picked This Workbook is because since I am using a template, I cannot know for certain the name of the workbook since the name of new workbooks based on the template will be prefixed with a number. Am I incorrect in assuming which I should use? I am using Excel 2003 if it is different from any other version also. "Jon Peltier" wrote: I don't know where this syntax came from: "ThisWorkbook.MyMacro" It should be assigned to "'My Workbook Name.xls'!MyMacro" Note that the workbook name is surrounded with single quotes if it contains dashes or spaces. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Wannabe" wrote in message ... I created a spreadsheet template that has some VBA code in it, then created two buttons to call the methods. This way, the person did not have to go to tools, macros and find the one they needed. But when I emailed the spreadsheet to the user, they pressed the toolbar button and it held a reference to my computer instead of the worksheet like I thought. When I originally set it up, I assigned the toolbar button to "ThisWorkbook.MyMacro" and thought this would carry over to another computer without messing up the reference. How can I prevent this from happening so a user will not have to reassign the toolbar buttons each time it is emailed? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links broken when spreadsheet emailed
That was it...I had my methods in the ThisWorkbook, thinking they would port
that way. Thanks. "Jon Peltier" wrote: If your irregular syntax comes from having the macros on the ThisWorkbook code module, you should move them to a regular module. Then assign them to the buttons as I described. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... I don't know where this syntax came from: "ThisWorkbook.MyMacro" It should be assigned to "'My Workbook Name.xls'!MyMacro" Note that the workbook name is surrounded with single quotes if it contains dashes or spaces. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Wannabe" wrote in message ... I created a spreadsheet template that has some VBA code in it, then created two buttons to call the methods. This way, the person did not have to go to tools, macros and find the one they needed. But when I emailed the spreadsheet to the user, they pressed the toolbar button and it held a reference to my computer instead of the worksheet like I thought. When I originally set it up, I assigned the toolbar button to "ThisWorkbook.MyMacro" and thought this would carry over to another computer without messing up the reference. How can I prevent this from happening so a user will not have to reassign the toolbar buttons each time it is emailed? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links broken when spreadsheet emailed
Just select the macro. The link will officially be "'Workbook
Name.xls'!MyMacro" or "'Template Name.xlt'!MyMacro". When you create a new file from the template containing the macro, the new file is automatically linked to the macro in the new file itself. If you copy the template in Windows Explorer and rename it, the renamed file when opened is linked to the macro in the renamed file. Working with an attached toolbar is problematic. It's better to have the workbook create its toolbar when it is opened and destroy it when it closes. John Walkenbach discusses the technique on his web site: http://www.j-walk.com/ss/excel/tips/tip53.htm - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Wannabe" wrote in message ... That syntax comes from when I choose customize toolbar and then right click the toolbar button and select assign macro. Then I can choose to use Macros in "This Workbook", "all open workbooks", and the one that has my spreadsheet name. The reason I picked This Workbook is because since I am using a template, I cannot know for certain the name of the workbook since the name of new workbooks based on the template will be prefixed with a number. Am I incorrect in assuming which I should use? I am using Excel 2003 if it is different from any other version also. "Jon Peltier" wrote: I don't know where this syntax came from: "ThisWorkbook.MyMacro" It should be assigned to "'My Workbook Name.xls'!MyMacro" Note that the workbook name is surrounded with single quotes if it contains dashes or spaces. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Wannabe" wrote in message ... I created a spreadsheet template that has some VBA code in it, then created two buttons to call the methods. This way, the person did not have to go to tools, macros and find the one they needed. But when I emailed the spreadsheet to the user, they pressed the toolbar button and it held a reference to my computer instead of the worksheet like I thought. When I originally set it up, I assigned the toolbar button to "ThisWorkbook.MyMacro" and thought this would carry over to another computer without messing up the reference. How can I prevent this from happening so a user will not have to reassign the toolbar buttons each time it is emailed? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Broken links but they're not | Excel Discussion (Misc queries) | |||
Relative links for emailed files | Excel Discussion (Misc queries) | |||
how can I fix Broken Links | Excel Programming | |||
Broken Links.... | Excel Discussion (Misc queries) | |||
Broken Links | Excel Discussion (Misc queries) |