Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro assignations follow new documents
I have an Excel spreadsheet with macros in it, and a custom toolbar which
has buttons assigned to the macros. I have also added a couple of items to the Tools menu and have assigned them to macros as well. The menu options are assigned to two of the macros on the custom toolbar. Everything works fine until I do Save As. After saving a copy of the spreadsheet (it imports data from a text file) and closing Excel, when I open the original spreadsheet, I find that the custom toolbar buttons and menu options have become assigned to the macros that are in the new spreadsheet I created. This results in the user having to do 'enable macros' a second time when they click a custom button/menu. If the Saved As spreadhseet has been deleted, renamed, or moved to a different directory, the user gets an error. This seems to happen whether I make the originating spreadsheet a normal ..xls or a template - saving the document changes the assignations in the template. I'd rather that the macro assignations didn't 'follow' the new spreadsheet, but stayed with the originating one. How can I make that happen? I tried making an autorun-on-open macro that would force the assignations, but didn't have any luck talking to the toolbar buttons (didn't try with the menus yet, but I don't plan to keep those - they were just for testing). Is there some setting that needs to be adjusted? Is this an issue and is there a fix? I'm using Windows 2000 and Office 2000. -- Wally www.forthsailing.com www.wally.myby.co.uk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro assignations follow new documents
You should add a macro that runs when your workbook is closed (Sub
Auto_Close or the Workbook_Close event handler) to delete your custom toolbars/menu items. Then when the original workbook is opened its custom additions will not be blocked by the ones belonging to the "save as" copy. -- Jim Rech Excel MVP "Wally" wrote in message ... |I have an Excel spreadsheet with macros in it, and a custom toolbar which | has buttons assigned to the macros. I have also added a couple of items to | the Tools menu and have assigned them to macros as well. The menu options | are assigned to two of the macros on the custom toolbar. | | Everything works fine until I do Save As. After saving a copy of the | spreadsheet (it imports data from a text file) and closing Excel, when I | open the original spreadsheet, I find that the custom toolbar buttons and | menu options have become assigned to the macros that are in the new | spreadsheet I created. | | This results in the user having to do 'enable macros' a second time when | they click a custom button/menu. If the Saved As spreadhseet has been | deleted, renamed, or moved to a different directory, the user gets an error. | This seems to happen whether I make the originating spreadsheet a normal | .xls or a template - saving the document changes the assignations in the | template. | | I'd rather that the macro assignations didn't 'follow' the new spreadsheet, | but stayed with the originating one. How can I make that happen? I tried | making an autorun-on-open macro that would force the assignations, but | didn't have any luck talking to the toolbar buttons (didn't try with the | menus yet, but I don't plan to keep those - they were just for testing). | | Is there some setting that needs to be adjusted? Is this an issue and is | there a fix? I'm using Windows 2000 and Office 2000. | | | -- | Wally | www.forthsailing.com | www.wally.myby.co.uk | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro assignations follow new documents
Jim Rech wrote:
You should add a macro that runs when your workbook is closed (Sub Auto_Close or the Workbook_Close event handler) to delete your custom toolbars/menu items. I had been wondering about this, but was reticent to try because I thought it might result in a global deletion of the custom toolbar. Then when the original workbook is opened its custom additions will not be blocked by the ones belonging to the "save as" copy. Okay, I'll give this a try. Ideally, I'd like to strip everything out of the SaveAs spreadsheet and turn it into a plain workbook with no macros or toolbars. I've just been messing around with the Workbook_BeforeSave event to create a new workbook and assign it an arbitrary name, which works, so I'll try deleting the custom toolbar/menu bits. -- Wally www.forthsailing.com www.wally.myby.co.uk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro assignations follow new documents
I don't know the details of your macro, but if it's a "utility macro" it's
best in an add-in. By a utility I mean macro that's handy to have no matter what workbook is active. That way the code never winds up in the saved workbook. It's much easier to never put the code in, than to strip it out afterward. Even if the code is specific to having a certain workbook open or a certain worksheet active it might still be a candidate to be in an add-in. In that case the code would check to see that the "conditions" are right, and alert the user if there are not. If you can't go the add-in route you might have your macro make a copy of the worksheet (ActiveSheet.Copy) and then save that. And then clear the data from the original. So the user would have two workbooks open unless you closed one in the code. All depends on what you want to do... -- Jim Rech Excel MVP "Wally" wrote in message ... | Jim Rech wrote: | | You should add a macro that runs when your workbook is closed (Sub | Auto_Close or the Workbook_Close event handler) to delete your custom | toolbars/menu items. | | I had been wondering about this, but was reticent to try because I thought | it might result in a global deletion of the custom toolbar. | | | Then when the original workbook is opened its | custom additions will not be blocked by the ones belonging to the | "save as" copy. | | Okay, I'll give this a try. Ideally, I'd like to strip everything out of the | SaveAs spreadsheet and turn it into a plain workbook with no macros or | toolbars. I've just been messing around with the Workbook_BeforeSave event | to create a new workbook and assign it an arbitrary name, which works, so | I'll try deleting the custom toolbar/menu bits. | | | | -- | Wally | www.forthsailing.com | www.wally.myby.co.uk | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro assignations follow new documents
Jim Rech wrote:
I don't know the details of your macro, but if it's a "utility macro" it's best in an add-in. By a utility I mean macro that's handy to have no matter what workbook is active. It's task-specific - it takes a plain text Material Requisition generated by another program and turns it into an Excel workbook (data is split into two worksheets). Its primary purpose is to speed up processing (which was all done manually beforehand). That way the code never winds up in the saved workbook. It's much easier to never put the code in, than to strip it out afterward. I haven't looked into add-ins, but it might be worth a try. Their IT people seem almost paranoid about security - eg, a laptop user can't change his mouse settings "because he has a glide-pad, therefore he doesn't need a mouse"(!). Given that, would doing it as an add-in possibly be a problem in terms of integrating it? Even if the code is specific to having a certain workbook open or a certain worksheet active it might still be a candidate to be in an add-in. In that case the code would check to see that the "conditions" are right, and alert the user if there are not. If you can't go the add-in route you might have your macro make a copy of the worksheet (ActiveSheet.Copy) and then save that. And then clear the data from the original. So the user would have two workbooks open unless you closed one in the code. All depends on what you want to do... The original is essentially two blank worksheets, a form data sheet, and an unpolulated 'template' sheet which is used as the basis for generating the formatting in the blank worksheets. The toolbar is associated with the originating sheet - my BeforeSave code can now find the toolbar and delete it from the new sheet, but leaves it intact in the originating doc. There can be any number of pages to be generated in the blank worksheets, so the single page from the template sheet is copied as many times as required, and then the imported/translated data is added. When the user saves the doc, I want to keep the two multi-page generated sheets and the form data one. If I can get the SaveAs dialog up in this code, I can get a new filename from the user and create the new sheet without the extraneous elements. I should then be able to finalise the behaviour so that they can start afresh by making a new sheet via the .xlt. I think... :-) -- Wally www.forthsailing.com www.wally.myby.co.uk |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro assignations follow new documents
The plot thickens.. and I'm getting lost<g. An add-in should not have any
particular security risks but it sounds that you're doing okay with the "macros in the workbook" approach. To prompt a user for a file name you can do something like this: Sub a() Dim FName As Variant FName = Application.GetSaveAsFilename If FName = False Then MsgBox "You canceled" Else MsgBox "You name is " & FName End If End Sub -- Jim Rech Excel MVP "Wally" wrote in message ... | Jim Rech wrote: | I don't know the details of your macro, but if it's a "utility macro" | it's best in an add-in. By a utility I mean macro that's handy to | have no matter what workbook is active. | | It's task-specific - it takes a plain text Material Requisition generated by | another program and turns it into an Excel workbook (data is split into two | worksheets). Its primary purpose is to speed up processing (which was all | done manually beforehand). | | | That way the code never | winds up in the saved workbook. It's much easier to never put the | code in, than to strip it out afterward. | | I haven't looked into add-ins, but it might be worth a try. Their IT people | seem almost paranoid about security - eg, a laptop user can't change his | mouse settings "because he has a glide-pad, therefore he doesn't need a | mouse"(!). Given that, would doing it as an add-in possibly be a problem in | terms of integrating it? | | | Even if the code is specific to having a certain workbook open or a | certain worksheet active it might still be a candidate to be in an | add-in. In that case the code would check to see that the | "conditions" are right, and alert the user if there are not. | | If you can't go the add-in route you might have your macro make a | copy of the worksheet (ActiveSheet.Copy) and then save that. And | then clear the data from the original. So the user would have two | workbooks open unless you closed one in the code. All depends on | what you want to do... | | The original is essentially two blank worksheets, a form data sheet, and an | unpolulated 'template' sheet which is used as the basis for generating the | formatting in the blank worksheets. The toolbar is associated with the | originating sheet - my BeforeSave code can now find the toolbar and delete | it from the new sheet, but leaves it intact in the originating doc. | | There can be any number of pages to be generated in the blank worksheets, so | the single page from the template sheet is copied as many times as required, | and then the imported/translated data is added. When the user saves the doc, | I want to keep the two multi-page generated sheets and the form data one. | | If I can get the SaveAs dialog up in this code, I can get a new filename | from the user and create the new sheet without the extraneous elements. I | should then be able to finalise the behaviour so that they can start afresh | by making a new sheet via the .xlt. I think... :-) | | | -- | Wally | www.forthsailing.com | www.wally.myby.co.uk | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro assignations follow new documents
Jim Rech wrote:
The plot thickens.. and I'm getting lost<g. An add-in should not have any particular security risks but it sounds that you're doing okay with the "macros in the workbook" approach. It's not that it's a potential security risk, but that the IT bods have stuff locked down so tight, they might not allow add-ins unless they specifically sanction the change. The laptop user hates the glide pad, but plugging in a USB mouse results in a pointer that moves too fast - and he's blocked from changing his mouse settings. Since he's argued, and failed, for access to the mouse settings, I doubt that IT would sanction an add-in. (Did it as an Excel macro rather than plain VB to get around them in the first place.) What's really bad is that he's the design office manager and has plenty of IT savvy - the automation routine that I'm working on was his idea. (sheesh!<tm) Anyway... To prompt a user for a file name you can do something like this: FName = Application.GetSaveAsFilename Yup, got that - nearly done. :-) -- Wally www.forthsailing.com www.wally.myby.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Follow Up Macro Question | Excel Discussion (Misc queries) | |||
Default PivotTable category assignations | Excel Discussion (Misc queries) | |||
Why can't I record "flag for follow up" as a Macro? | Excel Discussion (Misc queries) | |||
Record Excel 2003 Macro to link two documents | Excel Discussion (Misc queries) | |||
listing which documents were changed by macro | Excel Programming |