Protection help needed.
Protection help needed.
(OP)
I produce a couple of spreadsheets. (e.g. see density 12MF.xls at www.viscoanalyser.com/page8.html)
I have them password protected so that they can't access and change the calculations and they can only enter data in the unprotected cells.
As they enter data and select a calculation from a drop-down, it performs the calculation on all the data. Once they have done the calculation they can write the result into the tag space because the next data entry and calculation will change the results of the previous calculation. However, any calculation result can be recreated by re-selecting the calculation that produced it.
Once they have completed the spreadsheet I want to be able to protect the data entered but still allow the drops down calculations to work. I want to do this without them un-protecting the workbook or worksheet first; this would defeat the objective which is to create a record that once created is then protected against data corruption but where the calculations etc are at all times protected.
I'm pretty sure I can't do this through the protection menu but can it be done with a macro for example?
Or am I going to have to turn the whole thing into a VB application?
I have them password protected so that they can't access and change the calculations and they can only enter data in the unprotected cells.
As they enter data and select a calculation from a drop-down, it performs the calculation on all the data. Once they have done the calculation they can write the result into the tag space because the next data entry and calculation will change the results of the previous calculation. However, any calculation result can be recreated by re-selecting the calculation that produced it.
Once they have completed the spreadsheet I want to be able to protect the data entered but still allow the drops down calculations to work. I want to do this without them un-protecting the workbook or worksheet first; this would defeat the objective which is to create a record that once created is then protected against data corruption but where the calculations etc are at all times protected.
I'm pretty sure I can't do this through the protection menu but can it be done with a macro for example?
Or am I going to have to turn the whole thing into a VB application?





RE: Protection help needed.
RE: Protection help needed.
to protect with a password:
Sheets("Worksheet Name").Select
ActiveSheet.Protect "Password"
To unprotect
Sheets("Worksheet Name").Select
ActiveSheet.unprotect "Password"
if you have more than 1 sheet to protect/unprotect, add a for loop (e.g., for x = 1 to 3), add a varable (e.g., page$), then a page$=Choose(x, "Sheet1", "Sheet2", "Sheet3") and change "Worksheet Name" to page$.
RE: Protection help needed.
I have only one sheet visible and on that sheet most of the cells are locked, calculation results etc.
The cells for data entry are unlocked.
I'm far from expert with Excel so I may come back with some failed macros for more help.
Thanks again.
UPDATE:
Oops!
I selected Macro, Record new macro.
Then I selected [tools][protect sheet] (I had to un-protect to record the macro) ran down and unchecked the "Unlocked cells" under "allow user to select:" and gave it a password.
Clicked finished recording and wham; I'm locked out, the sheet is protected and I cannot select the unlocked cells.
That bit works a treat and I was expecting major hassles.
I even assigned a ctrl + shift + P short cut.
So I use the password I used in the macro, un-protect the sheet.
Then I go [tools] [Protect] and reselect allow user to select unlocked cells. Give it the usual password back again.
I entered some data.
Then used my short cut and it put a capital P in an unlocked cell!
So I undid that, clicked away from the unlocked cells and again used the shortcut.
It worked.
Simpler than I thought.
However, it used the most recent password not the one I used in the macro.
I can live with that.
But what I'd now like to do is not use a shortcut but attach the Macro to a button.
That ought to be easy enough.
JMW
www.ViscoAnalyser.com
RE: Protection help needed.
The radio button is a problem.
I can assign the macro to it but I have this catch 22 of the sheet being protected even though this isn't.
I am hoping if I get round this problem that though the button appears to be selectable to toggle on and off, once it functions it won't be selectable.
Don't like the radio button.
Still, if it works.
JMW
www.ViscoAnalyser.com
RE: Protection help needed.
RE: Protection help needed.
That did it.
I simply recorded a macro as I described above (without the shortcut) and attached it to the button. Actually, I'm in Excel 2002 and I did use a form button.
It seems to have worked a treat.
The data is protected and I can still toggle my calculations and get new calculation results.
Was there a reason you suggested not to use a form control button?
I only need this as a use once button. I don't need to reverse the action except by going into the tools menu and selecting un-protect sheet. This requires the password so that is fine. Once a user has clicked the button the data is secure against further tampering.
As secure as Excel password protection is, anyway.
Thanks again guys.
JMW
www.ViscoAnalyser.com
RE: Protection help needed.
Macros invoke security issues.
So I decided to add a digital signature which meant finding the office disk and adding in selfcert.exe.
Now catch 22 comes into play in a big way.
I created the certificate.
I went into the spreadsheet and [tools][security][add signature]. Fine.
Then I tried to save the file.
I got a message saying that saving the file would remove the certificate.
WTF?
So if I don't save I am done.
I need to be able to let people use this file. It is free. I don't want to go spend money on external certificates.
Is there a way round? How do I distribute the certificate with the file?
JMW
www.ViscoAnalyser.com
RE: Protection help needed.
As far as security with the macro, if it is in-house, and/or everyone knows that the file is safe because it came from you, then they just have to have the security setting to medium so that it will ask if the user wants to enable the macro.
RE: Protection help needed.
RE: Protection help needed.
RE: Protection help needed.
I'll just try it......
No. I guess it depends on what is protected. I have it set up so you can only select unprotected cells. Clicking on the corner square in the column and row headers doesn't work.
JMW
www.ViscoAnalyser.com
RE: Protection help needed.
another suggestion to consider . . .
without know the degree or quantity of calculations, but for major calcs, create/write custom functions in a VBA module.
once all the functions are written, you can then save the module as an xla file with protection, not accessible by users w/out password, and attach module with the main file. you can then do away with all the protection (except for the simple calcs or otherwise) and have the simple calcs do the other needed features.
this way a macro is not really needed to "unprotect" and "protect" the document as all the "critical" calcs are compiled (per se). and you do not need to worry about others "pirating" critical work.
something to ponder and good luck!
-pmover
RE: Protection help needed.
I have been thinking that sooner or later I will have to convert to an application rather than run it in excel.
That's going to take some doing for me. I'll need to do some learning for that.
JMW
www.ViscoAnalyser.com
RE: Protection help needed.
www.vbaexpress.com
Some of the best in vba will advise you.