 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007Protect cells with formulas only - Excel 2007
What is the best way to only protect cells with formulas in them? Many thanks  SignatureJohn F Excel 2007 XPproSP2 Not sure if there is an easier way, but you can: *Select the entire sheet (you can do this by clicking on the icon above row 1 and to the left of column A) *Right click in the sheet and select FORMAT CELLS *Go to the Protection tab and UNCHECK LOCKED *Now, go back to your sheet, right click on the cell(s) you want to protect and again select format cells *Go to the protection tab and CHECK LOCKED *When you are done selecting the cells to be locked go to the ribbon and select the review tab *Click on protect sheet *Enter a password This should result in a sheet containing selected cells that are password protected. > What is the best way to only protect cells with formulas in them? > > Many thanks Thanks Bob. I was hoping that there might be an easier way. SignatureJohn F Excel2007 XPproSP2 > Not sure if there is an easier way, but you can: > [quoted text clipped - 16 lines] >> >> Many thanks You could make it a little easier............. Sub lock_up() With ActiveSheet .Unprotect Password:="justme" .Cells.Locked = False .EnableSelection = xlUnlockedCells .UsedRange.Select End With For Each cell In Selection If cell.HasFormula Then cell.Locked = True Selection.FormulaHidden = False End If Next Range("A1").Select ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP >Thanks Bob. I was hoping that there might be an easier way. Thanks Gord. I haven't done any VBA coding before but things all start somewhere. Could you point me to a good starting point please. Many thanks SignatureJohn F Office2007 XPproSP2 > You could make it a little easier............. > [quoted text clipped - 20 lines] > >>Thanks Bob. I was hoping that there might be an easier way. John If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... I don't use 2007 yet but I'll give you the 2003 and earlier instructions. First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and Insert>Module. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to Tool>Macro>Macros. You can also assign this macro to a button or a shortcut key combo. NOTE: the code operates on whatever sheet is selected before running the macro. Gord >Thanks Gord. I haven't done any VBA coding before but things all start >somewhere. Could you point me to a good starting point please. > >Many thanks Excellent. Short term challenge solved and something to work on for the future .. an ideal solution. Many thanks. SignatureJohn F Excel2007 XPproSP2 > John > [quoted text clipped - 32 lines] >> >>Many thanks Thanks for the feedback John Keep at that VBA.........opens a whole new Excel and other Office apps world. Gord >Excellent. Short term challenge solved and something to work on for the >future .. an ideal solution. Many thanks.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.