Protect cells

[MrDoc]

Hi,

Is there a way to protect some cells of a spreadsheet from user input (by typing only), yet letting button controls and VBA macros modify the values of those cells?

I need to prevent users from accidentally deleting formulas or values generated by VBA macros or controls, although they may delete values in some cells.

Thanks for any help.

MrDoc

 

[joy_666]

Hi,

Well use the format cells option and under the protection tab select both the HIDDEN and LOCKED option (DO THIS FOR THE REQUIRED COLUMNS TO BE PROTECTED)

Then use the protect worksheet option and make sure to tick the SELECT LOCKED CELLS and SELECT UNLOCKED CELLS in the pop up....

U can use a password to protect the sheet......

 

[MrDoc]

Thanks, Joy 666, but I already tried that way. Users can't modify the values in the protected cells, but VBA macros and button controls also can't modify them, so they just don't work. Am I missing something?
MrDoc

 

[Jacophile]

Hi, the macros need to be tweaked to switch protection on and off using the protect and unprotect methods.

 

[Chris Bode]

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Well, you can allow user to enter values in certain cells and can protect other cells
1.Select the cells that you want to allow users to enter value
2.From the menu bar select tools>protection>allow user to edit ranges
3.Click on new button>click ok (nothing else to do)
4.Now click tools>protection>protect sheet
5.Give password and click OK>reenter password for confirmation
<o> </o>
Now users can only change values of the selected cells in step 1

 

[MrDoc]

Thank you, Jacophille. That would be a solution for the macros. So part of the problem would be solved. But the problem with the control tools remains: users can't change protected cells, but neither can the controls, like spinbuttons, for example. And I don't want users to mess with cells containing values obtained by clicking on spinbuttons or calculated by formulas.
Best Regards,
MrDoc

 

[MrDoc]

Chris Bode,

I tried your advice, but still macros and spinbuttons can't change values in protected cells. Seems we reached a dead end?

Thank you very much for replying!
MrDoc

 

[mikerickson]

Have you looked at the UserInterfaceOnly property of the worksheet .Protect method?

 

[MrDoc]

Mikerickson,

Thanks for replying. As far as I can see, that property would solve the problem concerning the VBA macros. Roughly, Unprotect - Modify - Protect. But how can I deal with spinbuttons created by using the control toolbox? The user must be able to use the controls to modify cell values, yet he or she isn't supposed to change cell values manually.

Best Regards,
MrDoc

 

[mikerickson]

If the Control is directly linked to a cell, both the control and the linked cell will need to be unlocked. If the control also runs a macro that fills cells the UserInterfaceOnly will take care of that situation.