Macro assignations follow new documents

W

Wally

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

Jim Rech

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

Wally

Jim said:
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.
Click to expand...

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.
Click to expand...

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

Jim Rech

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

Wally

Jim said:
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.
Click to expand...

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.
Click to expand...

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...
Click to expand...

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... :)
 
J

Jim Rech

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

Wally

Jim said:
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.
Click to expand...

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:
Click to expand...
FName = Application.GetSaveAsFilename
Click to expand...

Yup, got that - nearly done. :)
 
Top