Enabling/Disabling Macros
I have a template which contains a macro. I would prefer
the users who use this template not be aware the macro is there. i.e. I don't want any 'enable/disable macros Y/N' popup windows appearing. I don't want the users to be able to run the macro. The users will fill in the spreadsheet data and email it back to me. I will then run the macro to create a new worksheet from the enetered data. Is this possible? Or, is there a better way to do it? |
Enabling/Disabling Macros
David,
There are two parts to your question and different answers for each. I don't want any 'enable/disable macros Y/N' popup windows appearing. If a macro exists, it's going to be flagged when the workbook opens. If they have macros enabled, they won't get a warning. If they have them disabled, they also won't get a warning. If they have their security set to medium, they will get the warning.....but........you said I don't want the users to be able to run the macro. At the top of the module where the macro resides, place the following: Option Private Module With the above option, any macros in that module won't appear on the macro run list. Of course, if they're fluent in VBA, they could edit that module and delete that line. You could protect the VBA project but that password can be cracked too (just a warning) Anyway, if you don't think they're capable of getting at the code the Option Private Module should keep their grubby fingers from running your macro. John David wrote: I have a template which contains a macro. I would prefer the users who use this template not be aware the macro is there. i.e. I don't want any 'enable/disable macros Y/N' popup windows appearing. I don't want the users to be able to run the macro. The users will fill in the spreadsheet data and email it back to me. I will then run the macro to create a new worksheet from the enetered data. Is this possible? Or, is there a better way to do it? |
Enabling/Disabling Macros
David,
if users shouldnt be running the macro anyway you should take it out of the template.... when it comes back the code to process his data can be anywhere on your machine, no need to include it in the xlt. then the warning wont popup either. faik there's no way (and rightly so) to circumvent that popup if users have decided not to set macro security on low. If someone would publish a circumvention for that popup (if it could be done) he'd cause a REAL panic at MS :)... and with me too :( keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David" wrote: I have a template which contains a macro. I would prefer the users who use this template not be aware the macro is there. i.e. I don't want any 'enable/disable macros Y/N' popup windows appearing. I don't want the users to be able to run the macro. The users will fill in the spreadsheet data and email it back to me. I will then run the macro to create a new worksheet from the enetered data. Is this possible? Or, is there a better way to do it? |
Enabling/Disabling Macros
Thanks for the information.
Where are these security settings about macros to be found? If I send a user my template and he opens it and specifies 'disable macros', enters his data, saves it as an XLS and sends it back to me, will I still be able to run the macro or will it be disabled for me also? By the way this macro does nothing dangerous, all it does is perform calculations on the data entered and puts the results in a new worksheet. Its just that its irrelevant to the user so I'd rather they not be confused by seeing any reference to macros. - David -----Original Message----- David, There are two parts to your question and different answers for each. I don't want any 'enable/disable macros Y/N' popup windows appearing. If a macro exists, it's going to be flagged when the workbook opens. If they have macros enabled, they won't get a warning. If they have them disabled, they also won't get a warning. If they have their security set to medium, they will get the warning.....but........you said I don't want the users to be able to run the macro. At the top of the module where the macro resides, place the following: Option Private Module With the above option, any macros in that module won't appear on the macro run list. Of course, if they're fluent in VBA, they could edit that module and delete that line. You could protect the VBA project but that password can be cracked too (just a warning) Anyway, if you don't think they're capable of getting at the code the Option Private Module should keep their grubby fingers from running your macro. John David wrote: I have a template which contains a macro. I would prefer the users who use this template not be aware the macro is there. i.e. I don't want any 'enable/disable macros Y/N' popup windows appearing. I don't want the users to be able to run the macro. The users will fill in the spreadsheet data and email it back to me. I will then run the macro to create a new worksheet from the enetered data. Is this possible? Or, is there a better way to do it? . |
Enabling/Disabling Macros
David,
Where are these security settings about macros to be found? There's a good explanation in Excel (Tools/Macro/Security) The security settings are PC (User) specific. If your workbook doesn't depend on that macro to accomplish it's task, whatever settings they open it with won't matter at all. will I still be able to run the macro or will it be disabled for me also? If they e-mail the workbook to you and you have macros enabled, you'll be able to run it (after you alter the code and comment out that "Option Private Module" line of code) I'd rather they not be confused by seeing any reference to macros. If they have security set to Medium, they'll see the warning. If, as you say, it's not needed for the workbook and only you will be running it on your PC, Ray's suggestion of placing it in your Personal.xls workbook would seem like the best way to go. John David wrote: Thanks for the information. Where are these security settings about macros to be found? If I send a user my template and he opens it and specifies 'disable macros', enters his data, saves it as an XLS and sends it back to me, will I still be able to run the macro or will it be disabled for me also? By the way this macro does nothing dangerous, all it does is perform calculations on the data entered and puts the results in a new worksheet. Its just that its irrelevant to the user so I'd rather they not be confused by seeing any reference to macros. - David -----Original Message----- David, There are two parts to your question and different answers for each. I don't want any 'enable/disable macros Y/N' popup windows appearing. If a macro exists, it's going to be flagged when the workbook opens. If they have macros enabled, they won't get a warning. If they have them disabled, they also won't get a warning. If they have their security set to medium, they will get the warning.....but........you said I don't want the users to be able to run the macro. At the top of the module where the macro resides, place the following: Option Private Module With the above option, any macros in that module won't appear on the macro run list. Of course, if they're fluent in VBA, they could edit that module and delete that line. You could protect the VBA project but that password can be cracked too (just a warning) Anyway, if you don't think they're capable of getting at the code the Option Private Module should keep their grubby fingers from running your macro. John David wrote: I have a template which contains a macro. I would prefer the users who use this template not be aware the macro is there. i.e. I don't want any 'enable/disable macros Y/N' popup windows appearing. I don't want the users to be able to run the macro. The users will fill in the spreadsheet data and email it back to me. I will then run the macro to create a new worksheet from the enetered data. Is this possible? Or, is there a better way to do it? . |
Enabling/Disabling Macros
David,
all it does is perform calculations on the data entered and puts the results in a new worksheet. Another option.......... Don't use any macros in that workbook. Creat a new workbook just for yourself with a macro to open that other workbook, run your macro on it, create your new worksheet with calcs, etc. and save it. Again, the Personal.xls is also an option. John David wrote: Thanks for the information. Where are these security settings about macros to be found? If I send a user my template and he opens it and specifies 'disable macros', enters his data, saves it as an XLS and sends it back to me, will I still be able to run the macro or will it be disabled for me also? By the way this macro does nothing dangerous, all it does is perform calculations on the data entered and puts the results in a new worksheet. Its just that its irrelevant to the user so I'd rather they not be confused by seeing any reference to macros. - David -----Original Message----- David, There are two parts to your question and different answers for each. I don't want any 'enable/disable macros Y/N' popup windows appearing. If a macro exists, it's going to be flagged when the workbook opens. If they have macros enabled, they won't get a warning. If they have them disabled, they also won't get a warning. If they have their security set to medium, they will get the warning.....but........you said I don't want the users to be able to run the macro. At the top of the module where the macro resides, place the following: Option Private Module With the above option, any macros in that module won't appear on the macro run list. Of course, if they're fluent in VBA, they could edit that module and delete that line. You could protect the VBA project but that password can be cracked too (just a warning) Anyway, if you don't think they're capable of getting at the code the Option Private Module should keep their grubby fingers from running your macro. John David wrote: I have a template which contains a macro. I would prefer the users who use this template not be aware the macro is there. i.e. I don't want any 'enable/disable macros Y/N' popup windows appearing. I don't want the users to be able to run the macro. The users will fill in the spreadsheet data and email it back to me. I will then run the macro to create a new worksheet from the enetered data. Is this possible? Or, is there a better way to do it? . |
Enabling/Disabling Macros
Excel can't tell whether a macro is harmless or not. The security setting,
if not low, warns that there is a macro. It is then the users decision to enable or disable macros - but the prompt will not be surpressed - there is not setting for that. Keep the macro - get the warning Remove the macro (properly) - no warning. -- Regards, Tom Ogilvy "David" wrote in message ... The problem with that approach is the macro is likely to get lost since it wont be me who will be using it but any one of a number of developers. I'd prefer to keep the macro safely tucked away in the template. It does not do anything dangerous, its just not something the users need be aware of and it might confuse or scare them to see warning popups. - David -----Original Message----- David, if users shouldnt be running the macro anyway you should take it out of the template.... when it comes back the code to process his data can be anywhere on your machine, no need to include it in the xlt. then the warning wont popup either. faik there's no way (and rightly so) to circumvent that popup if users have decided not to set macro security on low. If someone would publish a circumvention for that popup (if it could be done) he'd cause a REAL panic at MS :)... and with me too :( keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David" wrote: I have a template which contains a macro. I would prefer the users who use this template not be aware the macro is there. i.e. I don't want any 'enable/disable macros Y/N' popup windows appearing. I don't want the users to be able to run the macro. The users will fill in the spreadsheet data and email it back to me. I will then run the macro to create a new worksheet from the enetered data. Is this possible? Or, is there a better way to do it? . |
All times are GMT +1. The time now is 12:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com