Protecting Formulas!!! 1

[abbeyv73]

I know that this is going to be very elementary excel knowledge, but this application is not my strong suit.
A user sent me a template that she is trying to create in Excel. She wants to protect the formulas to where no one can alter them.
How can you do this?

Thanks,
Abbey

 

[SkipVought]

Hi,
In Menu Item - Tools/Protection/Protect Sheet
you can turn on protection. This will protect every cell that has Format/Protection/Locked checkbox selected (the dafault)

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[abbeyv73]

I tried that. This will lock it from putting any data into the field. She just wants to set the formula, not specify the numerical data to be input.

 

[SkipVought]

You can't have it both ways in THAT cell.

You can UNLOCK another cell with NO formula in it for the purpose of Data Entry. Skip,
metzgsk@voughtaircraft.com

 

[abbeyv73]

Thank you Skip.
I told you that this wasn't my stronghold.
I will pass this info along.

 

[tomschafer]

Paste this into the workbook module.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

If ActiveCell.HasFormula = True Then
Cells(ActiveCell.Row, ActiveCell.Column + 1).Select
End If

End Sub

 

[acron]

Follow the following steps to allow data entry to cells other than formulas :

1. Highlight all cells (Ctrl-A)

2. Use Format | Cells | Protection

3. Uncheck the Locked check Box, i.e. unlock all cells

4. Press F5 (GoTo)

5. Click on Special

6. Select the Formulas Option (3rd one down)

7. Click Ok

8. Immediately Use Format | Cells | Protection

9. Place a tick in the Locked check box and also optionally Hidden

10. Click Ok

Now when you apply protection to the worksheet only the cells with formulas should be locked, allowing data entry to other cells.

You can do the same thing with the following VBA procedure :

Sub LockFormulas()
    Cells.Locked = False
    With Cells.SpecialCells(xlCellTypeFormulas, 23)
        .Locked = True
        .FormulaHidden = True
    End With
End Sub

Hope that helps,

AC

 

[jonsi]

Why not suggest to your User that they put their Input Cells on Sheet1 but put the formulae on Sheet2. Output the Results back to Sheet1 and protect Sheet2 as Skip suggested earlier.

You could even go so far as to make Sheet2 very hidden using View>Toolbars>Control Toolbox>Properties changing Visible sheet to 2-xlSheet Very hidden.

that way other users would not walk all over formulae.

...of course there's also Data>Validation

hwyl
Jonsi