protect formulas

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

Hi. Time for my daily question :) Thanks so much
everyone for helping me out on my other posts.

I am working to protect the formulas in my worksheet. If
possible, I want to be able to select cells with formulas
but not change them. I think its possible. (using excel
xp). This macro below tells me I don't have a variable
defined in the fourth line "for each c In Active...."

Any ideas?


Thanks,


Todd.



Sub ProtectFormula()
Dim Sh As Worksheet
Application.ScreenUpdating = False
For Each c In ActiveSheet.Cells 'in all worksheet
If c.Formula Like "=*" Then
c.Select
Selection.Locked = bLock
Selection.FormulaHidden = bLock
End If
Next c
End Sub
End Sub
 
Todd,

Just set the Protection property of the cell to Locked and protect the
worksheet. I think XP has another way to protect cells, check it out.
Than password protect the worksheet.

But keep in mind that Excel protection is easy to break...

As for "c" - add Dim c as Range to your code.
 
Thank you for the help. You were right about option
explicit. I ended up using this code below. It doesn't
work with Option Explicit turned on. But, it works without
it. So I think I will go with it. I have to learn more
about defining variables!

Todd


Private Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Worksheets
Sh.Select
Sh.Protect userinterfaceonly:=True
Sh.EnableSelection = xlUnlockedCells
Sh.EnableSelection = lockedCells
Next
Sheets(1).Select
Application.ScreenUpdating = True
End Sub
 
Todd,

Excel doesn't like this line:
Sh.EnableSelection = lockedCells
lockedCells is not defined and this line causes an error. With Option
Explicit turned off, you don't know that there is a problem. With it on,
Excel highlights the offending word. If you put your cursor somewhere on
or in the work and click F1 yoy will get a screen that say Keyword not
Defined. Meaning that Excel doesn't know what the h... you are talking
about (unless it is defined as a variable).

Sounds like you are trying to restrict the user to select unlocked cells
ONLY. So just get rid of that line...

Keep Exceling...
 
Back
Top