3 protected sheets, but need data from one sheet to still link to another when changed

**alisonhs** · 03-09-2015, 05:16 AM

Hi there, Excel experts. I am new to posting on this forum but I have used it quite a bit in the past to answer my many Excel questions. This time I can't seem to find the answer I'm looking for so I figured I'd post

So thank you both in retrospect and in advance!

My problem: I have an Excel workbook with 3 sheets. The first is a form with a bunch of check boxes (inserted with Developer/Form Controls), and the second two sheets are scoring sheets that tally and count the different boxes in sheet #1. I want to be able to protect all of the sheets so things cannot be moved and the formulas that derive the scores from the check boxes cannot be edited, but when I protect the scoring sheets, sheet #1 will no longer let me select the check boxes since I'd be changing boxes in "protected sheets."

I've been dabbling with VBA with this project but I am definitely not a pro, so I'd appreciate verrrry descriptive answers

Thanks again,
Alison

**TMS** · 03-09-2015, 05:59 AM

An initial thought: you could leave the scoring sheets unprotected but have worksheet activate and de-activate event handlers. In the sheet activate event, you would protect the worksheet and in the sheet de-activate you would un-protect it. Thus, when you are working on the input sheet, the sheets would be unprotected and should accept the linked data. BUT, if someone selects the sheets, they would be protected and they can't change them.

The issue with anything like this is that you have to ensure that the users enable macros. That requires more code to make the workbook useless unless the macros are activated. Typically, you would have a splash sheet which you make visible before you save and close the workbook, whilst making all the other sheets very hidden.

Hope that provides food for thought and research.

Regards, TMS