Protection help needed. 3

[jmw]

I produce a couple of spreadsheets. (e.g. see density 12MF.xls at

http://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?

JMW

http://www.ViscoAnalyser.com

 

[TDAA]

Not sure of the exact syntax, but you can use a macro to un-protect, modify, and re-protect.

 

[zelgar]

It's fairly easy to protect & unprotect sheets in a macro
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$.

 

[jmw]

Thanks guys,

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

http://www.ViscoAnalyser.com

 

[jmw]

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

http://www.ViscoAnalyser.com

 

[TDAA]

Insert an ActiveX Control Button (not a Form Control), and assign your macro to it. Adjust the format of the button to "unlock" the button so it can be accessed when the sheet is protected. A single click will allow it to run through your code each time.

 

[jmw]

TDAA, Thanks.
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

http://www.ViscoAnalyser.com

 

[jmw]

Well, not.
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

http://www.ViscoAnalyser.com

 

[TDAA]

I have 2007, and the form button does not allow a choice of lock/unlock. Perhaps the default is unlocked or the older version has the choice, but I didn't check that.

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.

 

[bernoullies123]

If you want to remove the protection and don't have the password, just download this add-in...

http://www.straxx.com/excel/password.html

 

[Dad1125]

In the upper left hand corner of the spreadsheet, click on the little square or use control-a to select the entire contents of the spreadsheet and copy/paste to a new workbook. (Do not use the right click on the tab to perform this function or you will copy the secutity also.) This should provide you a worksheet that is unprotected. I just tried this with the window and the structure protection and it copied the contents unprotected.

 

[jmw]

But will it bring with it any hidden sheets where calculations are performed and the results shown in the visible sheet?

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

http://www.ViscoAnalyser.com

 

[pmover]

jmw,

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

 

[jmw]

Thanks Pmover.
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

http://www.ViscoAnalyser.com

 

[Shrivallabha]

Whenever I had any hassles in coding and needed advice in vba then i turned up to:

http://www.vbaexpress.com

Some of the best in vba will advise you.