×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Protection help needed.
3

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?

JMW
www.ViscoAnalyser.com

 

RE: Protection help needed.

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

RE: Protection help needed.

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

RE: Protection help needed.

(OP)
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
www.ViscoAnalyser.com

 

RE: Protection help needed.

(OP)
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
www.ViscoAnalyser.com

 

RE: Protection help needed.

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.

RE: Protection help needed.

(OP)
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
www.ViscoAnalyser.com

 

RE: Protection help needed.

(OP)
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
www.ViscoAnalyser.com

 

RE: Protection help needed.

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.

RE: Protection help needed.

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.

RE: Protection help needed.

(OP)
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
www.ViscoAnalyser.com

 

RE: Protection help needed.

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

RE: Protection help needed.

(OP)
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
www.ViscoAnalyser.com

 

RE: Protection help needed.

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

Some of the best in vba will advise you.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources