Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
Hi everyone
Just when I thought I had completed my latest project I find I have fallen foul to the simplest of things! I have created an Excel database <because our PCs dont have Access. I denied access to the speadsheet by using a userform on opening and blocking all methods of getting past the form to the sheet behind. All entries are via the userform. When the userform is closed the application closes! Access to the sheet is via a textbox which requires a password. All was working fine until I discovered if you alter Excels macro security settings to prompt wether you want to disable macros anyone can access the sheet simply by turning macros off when prompted during file opening. Is there any way round this? If I set everyones macro security to low they will still be able to get in by resetting the security level. Is there any code I could put into the sheet to alter the setting or check the setting and stop the file opening if the macros are to be disabled? Thanks in advance Kenny Win 2000 Office 97 and 2000 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
I afraid it's always up to the user whether he/she wants the macros in a workbook to execute or not. Otherwise, someone can write a virus and force-execute it on anyone's computer.
If your workbook contains sensitive data, you can also hide your worksheets and the password-protect the structure of the workbook. Regards, Edwin Tam http://www.vonixx.com "N E Body" wrote: Hi everyone Just when I thought I had completed my latest project I find I have fallen foul to the simplest of things! I have created an Excel database <because our PCs dont have Access. I denied access to the speadsheet by using a userform on opening and blocking all methods of getting past the form to the sheet behind. All entries are via the userform. When the userform is closed the application closes! Access to the sheet is via a textbox which requires a password. All was working fine until I discovered if you alter Excels macro security settings to prompt wether you want to disable macros anyone can access the sheet simply by turning macros off when prompted during file opening. Is there any way round this? If I set everyones macro security to low they will still be able to get in by resetting the security level. Is there any code I could put into the sheet to alter the setting or check the setting and stop the file opening if the macros are to be disabled? Thanks in advance Kenny Win 2000 Office 97 and 2000 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
Hello Kenny
There is no way around macro security settings. If your wish is to prevent users from seeing your sheet in case macro security is set to low then in VBE set the visible property of your sheet to xlsheetveryhidden and password protect your project (remember to save changes). Access to your sheet will only be possible via VBE by unprotecting the project with your password and setting the sheet visible property to xlsheetvisible. HTH Cordially Pascal "N E Body" a écrit dans le message de ... Hi everyone Just when I thought I had completed my latest project I find I have fallen foul to the simplest of things! I have created an Excel database <because our PCs dont have Access. I denied access to the speadsheet by using a userform on opening and blocking all methods of getting past the form to the sheet behind. All entries are via the userform. When the userform is closed the application closes! Access to the sheet is via a textbox which requires a password. All was working fine until I discovered if you alter Excels macro security settings to prompt wether you want to disable macros anyone can access the sheet simply by turning macros off when prompted during file opening. Is there any way round this? If I set everyones macro security to low they will still be able to get in by resetting the security level. Is there any code I could put into the sheet to alter the setting or check the setting and stop the file opening if the macros are to be disabled? Thanks in advance Kenny Win 2000 Office 97 and 2000 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
"N E Body" wrote ...
I have created an Excel database <because our PCs dont have Access. Neither does mine, I somehow managed to break the installation and can't afford the time to rebuild my machine just now. And I'm responsible for an app that is advertised as having an 'Access' backend database!! Happily, I can get by just using ADO (actually, I've found that I can do more with ADO than the MS Access UI allows). Using the excuse of not having MS Access is just being defeatist <g. Jamie. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
One method of making sure macros are turned on is:
* Create a new worksheet. Place a large block of text on this sheet explaining that the user must have macros turned on to use the file. * Set the data pages of the file to VeryHidden, leaving only the text block visible to the user. * Set an AutoOpen macro that sets the opening page to VeryHidden, sets the data pages to Visible, and opens the UserForm. HTH Ed "N E Body" wrote in message ... Hi everyone Just when I thought I had completed my latest project I find I have fallen foul to the simplest of things! I have created an Excel database <because our PCs dont have Access. I denied access to the speadsheet by using a userform on opening and blocking all methods of getting past the form to the sheet behind. All entries are via the userform. When the userform is closed the application closes! Access to the sheet is via a textbox which requires a password. All was working fine until I discovered if you alter Excels macro security settings to prompt wether you want to disable macros anyone can access the sheet simply by turning macros off when prompted during file opening. Is there any way round this? If I set everyones macro security to low they will still be able to get in by resetting the security level. Is there any code I could put into the sheet to alter the setting or check the setting and stop the file opening if the macros are to be disabled? Thanks in advance Kenny Win 2000 Office 97 and 2000 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
Thanks Ed
I wish I had received this post earlier - I have messed about all day and arrived at the same result! I dont know what this VeryHidden is that everyone keeps suggesting. I hide the sheets on closing and password protect the workbook. Because you cannot have all sheets hidden I left a blank sheet. An auto open macro unprotects the workbook and makes the sheets visible. If the file is opened without running macros and the workbook remains protected. My blank sheet now has a "turn on macros" message - thanks Ed. Regards Kenny "Ed" wrote in message ... One method of making sure macros are turned on is: * Create a new worksheet. Place a large block of text on this sheet explaining that the user must have macros turned on to use the file. * Set the data pages of the file to VeryHidden, leaving only the text block visible to the user. * Set an AutoOpen macro that sets the opening page to VeryHidden, sets the data pages to Visible, and opens the UserForm. HTH Ed "N E Body" wrote in message ... Hi everyone Just when I thought I had completed my latest project I find I have fallen foul to the simplest of things! I have created an Excel database <because our PCs dont have Access. I denied access to the speadsheet by using a userform on opening and blocking all methods of getting past the form to the sheet behind. All entries are via the userform. When the userform is closed the application closes! Access to the sheet is via a textbox which requires a password. All was working fine until I discovered if you alter Excels macro security settings to prompt wether you want to disable macros anyone can access the sheet simply by turning macros off when prompted during file opening. Is there any way round this? If I set everyones macro security to low they will still be able to get in by resetting the security level. Is there any code I could put into the sheet to alter the setting or check the setting and stop the file opening if the macros are to be disabled? Thanks in advance Kenny Win 2000 Office 97 and 2000 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
"Ed" wrote...
One method of making sure macros are turned on is: * Create a new worksheet. Place a large block of text on this sheet explaining that the user must have macros turned on to use the file. * Set the data pages of the file to VeryHidden, leaving only the text block visible to the user. * Set an AutoOpen macro that sets the opening page to VeryHidden, sets the data pages to Visible, and opens the UserForm. ... Depending on the level of user sophistication/perversity, users could open this workbook with macros disabled, then create a new workbook with macros enabled, and write macros in the second workbook to manipulate the first, unprotecting and unhiding ABSOLUTELY EVERYTHING in the first workbook. No big deal to use an internal password cracker first on the original workbook to make sure it's completely unprotected. Welcome to Excel development. There is no robust way to protect any content stored in any Office application's document files. Except . . . not entirely secure, but requiring more user sophistication to crack, use a File Open password for your workbook. These are MUCH HARDER to crack than internal passwords. Then use another workbook to open the now-protected workbook via macros with macros enabled in it. The user could still break into the second workbook and step through the macros, but as I said above it'd require more sophistication. Along this line, you could also write a small EXE using Automation (the facility formerly known as OLE) to open or link to Excel and open the now-protected workbook. It may be possible for users to step through such an EXE using a debugger, but now it'd really take a high level of sophistication to beat the system. -- To top-post is human, to bottom-post and snip is sublime. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
"Harlan Grove" wrote in message
... <<snip Welcome to Excel development. There is no robust way to protect any content stored in any Office application's document files. I guess it depends on whether you're trying to protect sensitive or proprietary information from being available to unauthorized hackers, or simply wanting to prevent fumble-fingered non-thinkers from screwing up your data! I created a workbook one time: the visible front sheet was available for use as a data input form, while the second sheet was hidden, collecting the data and sorting it into a structure the database would easily import. People kept screwing with the front sheet, which messed up my second sheet. Had some frustrated phone calls when they couldn't get through the password to make the first sheet "prettier" - but I kept my data input structure safe! Ed |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
Kenny -
These two Knowledge Base articles on VeryHidden might help you. http://support.microsoft.com/default...=xlw#appliesto http://support.microsoft.com/default...92&Product=xlw Ed "N E Body" wrote in message ... Thanks Ed I wish I had received this post earlier - I have messed about all day and arrived at the same result! I dont know what this VeryHidden is that everyone keeps suggesting. I hide the sheets on closing and password protect the workbook. Because you cannot have all sheets hidden I left a blank sheet. An auto open macro unprotects the workbook and makes the sheets visible. If the file is opened without running macros and the workbook remains protected. My blank sheet now has a "turn on macros" message - thanks Ed. Regards Kenny "Ed" wrote in message ... One method of making sure macros are turned on is: * Create a new worksheet. Place a large block of text on this sheet explaining that the user must have macros turned on to use the file. * Set the data pages of the file to VeryHidden, leaving only the text block visible to the user. * Set an AutoOpen macro that sets the opening page to VeryHidden, sets the data pages to Visible, and opens the UserForm. HTH Ed "N E Body" wrote in message ... Hi everyone Just when I thought I had completed my latest project I find I have fallen foul to the simplest of things! I have created an Excel database <because our PCs dont have Access. I denied access to the speadsheet by using a userform on opening and blocking all methods of getting past the form to the sheet behind. All entries are via the userform. When the userform is closed the application closes! Access to the sheet is via a textbox which requires a password. All was working fine until I discovered if you alter Excels macro security settings to prompt wether you want to disable macros anyone can access the sheet simply by turning macros off when prompted during file opening. Is there any way round this? If I set everyones macro security to low they will still be able to get in by resetting the security level. Is there any code I could put into the sheet to alter the setting or check the setting and stop the file opening if the macros are to be disabled? Thanks in advance Kenny Win 2000 Office 97 and 2000 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and file security
Ed
Yep it sounds as though Im following your footsteps here! I am just stopping fumblers. All they get on opening is a userform for data entry. Dates can only be entered by a button so they cant screw up the date formatting. Even signing off has to be via a code number so there is no blaming anyone else. I have even blocked anyone changing recalled information, The devil in me has set it to allow them to change the text within the fields and save as usual - what they have not realized is changed text does not get to the database!!! Thanks for the links to VeryHidden Kenny www.handpicked.co.uk/crathornehall "Ed" wrote in message ... "Harlan Grove" wrote in message ... <<snip Welcome to Excel development. There is no robust way to protect any content stored in any Office application's document files. I guess it depends on whether you're trying to protect sensitive or proprietary information from being available to unauthorized hackers, or simply wanting to prevent fumble-fingered non-thinkers from screwing up your data! I created a workbook one time: the visible front sheet was available for use as a data input form, while the second sheet was hidden, collecting the data and sorting it into a structure the database would easily import. People kept screwing with the front sheet, which messed up my second sheet. Had some frustrated phone calls when they couldn't get through the password to make the first sheet "prettier" - but I kept my data input structure safe! Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Security and macros | Excel Discussion (Misc queries) | |||
Macros and security | Excel Worksheet Functions | |||
Macros security | Excel Discussion (Misc queries) | |||
Macros/Security | Excel Worksheet Functions | |||
security for my macros | Excel Programming |