Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
Hi folks
Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox "print now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
A possible solution might be to set a flag on a sheet which is very hidden.
The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox "print now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
Hi jim, you remember me (your student¡¡¡¡)
Also could be a good idea Hide all the toolbars and just leave a button with the print caption in it (an its respective macro of print) Application.CommandBars("").Enabled = false Application.CommandBars("format").Enabled = false Application.CommandBars("form").Enabled = false Application.CommandBars("chart").Enabled = false Application.commandbars("Worksheet Menu Bar").enabled=false remember to put this code in your workbook module and be shure to create a macro to enable all your menus with a shortcut application.commandbars("Worksheet Menu Bar").enabled=true 'etc etc. also I want your opinion jim "Jim Thomlinson" wrote: A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox "print now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
I was under the impression that the beforeprint event doesn't run when
macros are enabled. Since the OP stated that disabling macros was the root of the whole problem, how would you see this being a solution? -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox "print now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
Looks like you fell into the same trap as your instructor: You have
presented VBA code as a solution, but . . . I was under the impression that vba macros don't run when macros are disabled. Since the OP stated that disabling macros was the root of the whole problem, how would you see this being a solution? -- Regards, Tom Ogilvy "filo666" wrote in message ... Hi jim, you remember me (your student¡¡¡¡) Also could be a good idea Hide all the toolbars and just leave a button with the print caption in it (an its respective macro of print) Application.CommandBars("").Enabled = false Application.CommandBars("format").Enabled = false Application.CommandBars("form").Enabled = false Application.CommandBars("chart").Enabled = false Application.commandbars("Worksheet Menu Bar").enabled=false remember to put this code in your workbook module and be shure to create a macro to enable all your menus with a shortcut application.commandbars("Worksheet Menu Bar").enabled=true 'etc etc. also I want your opinion jim "Jim Thomlinson" wrote: A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
"Enabled" should of course be "Disabled". My Typo.
-- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I was under the impression that the beforeprint event doesn't run when macros are enabled. Since the OP stated that disabling macros was the root of the whole problem, how would you see this being a solution? -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
You need to be able to handle the eventuallity of opening the file with
macro's disabled. The on close event of the spreadsheet should hide all but a single sheet which lets the user know that macro's must be enabled. The on open hides this sheet and unhides the data sheets. Other than that I can not see how to get around this whole issue. Good point though... HTH "Tom Ogilvy" wrote: I was under the impression that the beforeprint event doesn't run when macros are enabled. Since the OP stated that disabling macros was the root of the whole problem, how would you see this being a solution? -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox "print now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
A typo. How many mistakes does that make for you in your life. I am
guessing... 1. I should we mark this day on the callendar for posterity... ;-) "Tom Ogilvy" wrote: "Enabled" should of course be "Disabled". My Typo. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I was under the impression that the beforeprint event doesn't run when macros are enabled. Since the OP stated that disabling macros was the root of the whole problem, how would you see this being a solution? -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
you're right tom, but think about this, if the programmer leaves the
Application.commandbars("Worksheet Menu Bar").enabled=true (and I mean he leave excel with no bars in it) how does the user could disabled the macros??????? try it. "Tom Ogilvy" wrote: "Enabled" should of course be "Disabled". My Typo. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I was under the impression that the beforeprint event doesn't run when macros are enabled. Since the OP stated that disabling macros was the root of the whole problem, how would you see this being a solution? -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
That is a nice touch but it can lead to a lot of work. Those are application
settings and when you paly with those you really want to have an error handler to clean those settings up in case of an error. The other issue is if the user has multiple books open then the toolbars are gone for all books, not just the current book. I usually avoid that wherever possible. Too many headaches... "filo666" wrote: Hi jim, you remember me (your student¡¡¡¡) Also could be a good idea Hide all the toolbars and just leave a button with the print caption in it (an its respective macro of print) Application.CommandBars("").Enabled = false Application.CommandBars("format").Enabled = false Application.CommandBars("form").Enabled = false Application.CommandBars("chart").Enabled = false Application.commandbars("Worksheet Menu Bar").enabled=false remember to put this code in your workbook module and be shure to create a macro to enable all your menus with a shortcut application.commandbars("Worksheet Menu Bar").enabled=true 'etc etc. also I want your opinion jim "Jim Thomlinson" wrote: A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox "print now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
Can't you use formulas to make the display of the information depend on the
required fields being completed? That way they might print it, but there wouldn't be anything to print. Still relatively easy to get around though... As you note, this seems like an impossible requirement based on the info you provided. -- Tim Williams Palo Alto, CA "Paul Wagstaff" wrote in message . uk... Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox "print now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
How could the user open the workbook with no menus. and for any workbook
that is opened (given you leave that option), the user could disable the macros - that has nothing to do with menus. That is a security setting - or the user can hold down the shift key. If you actually meant how could the user print, there are short cuts to allow that - but since macros have been disabled, the menus are never altered. Of course, regardless of any steps, the user can print a file using Drag and Drop by putting a print Icon on the desktop and dragging the file from windows explorer to the icon and dropping it. then select print in the dialog. (tested with win98 SE but I would expect all the operating systems to support this - if not the properties for the xls extension could be altered to do it on a click). Doubt there is much control one has there, but haven't played with it. -- Regards, Tom Ogilvy "filo666" wrote in message ... you're right tom, but think about this, if the programmer leaves the Application.commandbars("Worksheet Menu Bar").enabled=true (and I mean he leave excel with no bars in it) how does the user could disabled the macros??????? try it. "Tom Ogilvy" wrote: "Enabled" should of course be "Disabled". My Typo. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I was under the impression that the beforeprint event doesn't run when macros are enabled. Since the OP stated that disabling macros was the root of the whole problem, how would you see this being a solution? -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
If macros are disabled, then they are disabled. No open or close macros.
The user opens the file with macros disabled breaks any passwords for workbook or sheet level protection. Unhides sheets to their hearts content. Prints wildly. You can get code to break those passwords right here. But you say, macros are disabled. Only in that workbook. They work in another open workbook with the code to break the password. Better would probably be an addin that creates the workbook when needed and with the security options in xl2002 and xl2003, that may be a weak approach as well. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... You need to be able to handle the eventuallity of opening the file with macro's disabled. The on close event of the spreadsheet should hide all but a single sheet which lets the user know that macro's must be enabled. The on open hides this sheet and unhides the data sheets. Other than that I can not see how to get around this whole issue. Good point though... HTH "Tom Ogilvy" wrote: I was under the impression that the beforeprint event doesn't run when macros are enabled. Since the OP stated that disabling macros was the root of the whole problem, how would you see this being a solution? -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A possible solution might be to set a flag on a sheet which is very hidden. The user can not get to the very hidden sheet (providing the project is password protected) so you can control how often a sheet can be printed. You will probably want to use the before print event to check the very hidden sheet to determine if the sheet can be printed... HTH "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling macros is not an option!
Hi Paul,
One possibility that would be secure at least within the VBA password would be to use a workbook with only the required number of worksheets €“ all of them blank (i.e. Scratch sheets). Then only populate, format, set named ranges, page setups, etc. for them via a workbook open event trigger in VBA code, hence requiring the VBA code to be functional to see the form. If there are large text sections in the form, you might store them as disjointed snippets someplace out of the way on one of the sheets to avoid having to include them within the VBA code (Only if these snippets are non-confidential of course and use VBA code to place and format them for display). Finally, use a before save event trigger to clear all worksheets of everything but any text snippets you might use. Without a better idea of your workbook contents, I cant know how practical or impractical this approach might be. Best Regards, Walt "Paul Wagstaff" wrote: Hi folks Office 97 & NT4 I've been handed a bit of a poisened chalice & like a fool I've taken a sip...so here's the problem: Esentially I need to prohibit printing a file unless a small number of fields are completed. This was originally done in MS Word, but nothing stops the users from choosing 'Disable Macros' and printing out as many copies as they like. I've come across XML macros that cannot be disabled, so I think Excel is my only choice (yes?). What I'd like to do then is create a spreadsheet that contains some text fields, which are validated and upon completion, prompt the user to print. I can handle the validation & the final msgbox "print now?" etc, but am completely stumped re. creating code from previous versions of Excel. Can anyone help me or suggest an alternative way to solve this problem? I don't mind how it's done as long as users can't print a (partially or totally) incomplete form - that's the nub of the issue Ideally I'd like to use Word, and not use UserForms, but beggars can't be choosers. Thanks, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disabling macros | Excel Discussion (Misc queries) | |||
Disabling Macros | Excel Programming | |||
Disabling Macros | Excel Programming | |||
Disabling/Enabling Macros | Excel Programming | |||
Option buttons - disabling | Excel Programming |