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.

--
sb
"Todd" <tunatl@hotmail wrote in message
...
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

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
-----Original Message-----
Todd,

Sounds like you don't have Option Explicit at the top of
your modules.
Having this and using the Compile in VBA would help you
find these errors.
There is a setting (I think it is in the Tools menu) that
will automatically
do this for you (but only on new modules, not the old
ones).

Seems like "block" is a variable and needs to be defined
Dim bLock as ???
bLock = ???

see if that helps...

--
sb
"Todd" wrote in message
...
Thanks, I want to have this auto protect upon open.
The
idea is to reprotect automatically in case users remove
the protection. I am hoping that if they can select a
locked cell to see where the formula got its result
from,
it will cut down on how often they unprotect the
sheet. "Adding Dim C As Range" seems to have helped.
But
now it says "Selection.Locked = bLock" is not defined.

Todd


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



-----Original Message-----
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.

--
sb
"Todd" <tunatl@hotmail wrote in message
...
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,

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...

--
sb
"Todd" wrote in message
...
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
-----Original Message-----
Todd,

Sounds like you don't have Option Explicit at the top of
your modules.
Having this and using the Compile in VBA would help you
find these errors.
There is a setting (I think it is in the Tools menu) that
will automatically
do this for you (but only on new modules, not the old
ones).

Seems like "block" is a variable and needs to be defined
Dim bLock as ???
bLock = ???

see if that helps...

--
sb
"Todd" wrote in message
...
Thanks, I want to have this auto protect upon open.
The
idea is to reprotect automatically in case users remove
the protection. I am hoping that if they can select a
locked cell to see where the formula got its result
from,
it will cut down on how often they unprotect the
sheet. "Adding Dim C As Range" seems to have helped.
But
now it says "Selection.Locked = bLock" is not defined.

Todd


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



-----Original Message-----
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.

--
sb
"Todd" <tunatl@hotmail wrote in message
...
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




.



.