"Todd" wrote in message
...
Is there a way to select all cells without a formula and
unlock them? Maybe a macro? I want to protect all the
cells with formulas and leave the rest open to changes.
Right now all cells with data are locked. I have a lot
of worksheets so doing this manually will take a LONG time.


TIA

Todd

Todd,
Open the VB editor for the workbook in question. Insert a module and
then paste the following code into the module. Then run it whenever you
need to. (May not be the most elegant way, but it worked for me). [BTW you
can also use edit, goto, special to select cells with formulas. Then right
click on the selection and make the changes. You would have to do it
separately for every sheet though]
Good luck!

Bob L.


Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets

mysheet.Activate
Set myrange = mysheet.UsedRange

For Each mycell In myrange

Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty cells

Case Else
'distinguish between constants and formulas
If Left(CStr(mycell.Formula), 1) = "=" Then
mycell.Locked = True
Else
mycell.Locked = False
End If

End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub

I tried the macro and I am getting the same error
message "unable to set locked property of the range class"?

Is there an option or preference or something I selected
to make this happen? Two macros giving the same error, I
must be doing something wrong.


Todd.

-----Original Message-----

"Todd" wrote in message
...
Is there a way to select all cells without a formula and
unlock them? Maybe a macro? I want to protect all the
cells with formulas and leave the rest open to changes.
Right now all cells with data are locked. I have a lot
of worksheets so doing this manually will take a LONG
time.


TIA

Todd

Todd,
Open the VB editor for the workbook in question.
Insert a module and
then paste the following code into the module. Then run
it whenever you
need to. (May not be the most elegant way, but it worked
for me). [BTW you
can also use edit, goto, special to select cells with
formulas. Then right
click on the selection and make the changes. You would
have to do it
separately for every sheet though]
Good luck!

Bob L.


Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets

mysheet.Activate
Set myrange = mysheet.UsedRange

For Each mycell In myrange

Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty
cells

Case Else
'distinguish between constants and
formulas
If Left(CStr(mycell.Formula), 1) = "="
Then
mycell.Locked = True
Else
mycell.Locked = False
End If

End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub




.

"Todd" wrote in message
...
I tried the macro and I am getting the same error
message "unable to set locked property of the range class"?

Is there an option or preference or something I selected
to make this happen? Two macros giving the same error, I
must be doing something wrong.


Todd.

-----Original Message-----

"Todd" wrote in message
...
Is there a way to select all cells without a formula and
unlock them? Maybe a macro? I want to protect all the
cells with formulas and leave the rest open to changes.
Right now all cells with data are locked. I have a lot
of worksheets so doing this manually will take a LONG
time.


TIA

Todd

Todd,
Open the VB editor for the workbook in question.
Insert a module and
then paste the following code into the module. Then run
it whenever you
need to. (May not be the most elegant way, but it worked
for me). [BTW you
can also use edit, goto, special to select cells with
formulas. Then right
click on the selection and make the changes. You would
have to do it
separately for every sheet though]
Good luck!

Bob L.


Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets

mysheet.Activate
Set myrange = mysheet.UsedRange

For Each mycell In myrange

Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty
cells

Case Else
'distinguish between constants and
formulas
If Left(CStr(mycell.Formula), 1) = "="
Then
mycell.Locked = True
Else
mycell.Locked = False
End If

End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub




.


Todd,
Your problem is that the sheets are protected so cannot be locked. I
added a line of code to unprotect the sheets first, and then protect them
after doing the locks.

Here it is:
Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets
mysheet.Unprotect
mysheet.Activate
Set myrange = mysheet.UsedRange

For Each mycell In myrange

Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty cells

Case Else
'distinguish between constants and formulas
If Left(CStr(mycell.Formula), 1) = "=" Then
mycell.Locked = True
Else
mycell.Locked = False
End If

End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub
Let me know if that takes care of it.

Bob L.

Hi Bob,

Thanks for the help. Its still not working. It does run
though. Its not getting error messages like before.
The macro runs but does not unlock any cells. I ran it on
sheets that were both protected and unprotected and I made
a sheet from scratch with both simple equations and text
only cells. I can't get it to work.

Thanks again,


Todd.


-----Original Message-----

"Todd" wrote in message
...
I tried the macro and I am getting the same error
message "unable to set locked property of the range
class"?

Is there an option or preference or something I selected
to make this happen? Two macros giving the same error,
I
must be doing something wrong.


Todd.

-----Original Message-----

"Todd" wrote in message
...
Is there a way to select all cells without a formula
and
unlock them? Maybe a macro? I want to protect all
the
cells with formulas and leave the rest open to
changes.
Right now all cells with data are locked. I have a
lot
of worksheets so doing this manually will take a LONG
time.


TIA

Todd

Todd,
Open the VB editor for the workbook in question.
Insert a module and
then paste the following code into the module. Then
run
it whenever you
need to. (May not be the most elegant way, but it
worked
for me). [BTW you
can also use edit, goto, special to select cells with
formulas. Then right
click on the selection and make the changes. You would
have to do it
separately for every sheet though]
Good luck!

Bob L.


Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets

mysheet.Activate
Set myrange = mysheet.UsedRange

For Each mycell In myrange

Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty
cells

Case Else
'distinguish between constants and
formulas
If Left(CStr(mycell.Formula), 1) = "="
Then
mycell.Locked = True
Else
mycell.Locked = False
End If

End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub




.


Todd,
Your problem is that the sheets are protected so
cannot be locked. I
added a line of code to unprotect the sheets first, and
then protect them
after doing the locks.

Here it is:
Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets
mysheet.Unprotect
mysheet.Activate
Set myrange = mysheet.UsedRange

For Each mycell In myrange

Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty
cells

Case Else
'distinguish between constants and
formulas
If Left(CStr(mycell.Formula), 1) = "="
Then
mycell.Locked = True
Else
mycell.Locked = False
End If

End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub
Let me know if that takes care of it.

Bob L.


.

"Todd" wrote in message
...
Hi Bob,

Thanks for the help. Its still not working. It does run
though. Its not getting error messages like before.
The macro runs but does not unlock any cells. I ran it on
sheets that were both protected and unprotected and I made
a sheet from scratch with both simple equations and text
only cells. I can't get it to work.

Thanks again,


Todd.


-----Original Message-----

"Todd" wrote in message
...
I tried the macro and I am getting the same error
message "unable to set locked property of the range
class"?

Is there an option or preference or something I selected
to make this happen? Two macros giving the same error,
I
must be doing something wrong.


Todd.

-----Original Message-----

"Todd" wrote in message
...
Is there a way to select all cells without a formula
and
unlock them? Maybe a macro? I want to protect all
the
cells with formulas and leave the rest open to
changes.
Right now all cells with data are locked. I have a
lot
of worksheets so doing this manually will take a LONG
time.


TIA

Todd

Todd,
Open the VB editor for the workbook in question.
Insert a module and
then paste the following code into the module. Then
run
it whenever you
need to. (May not be the most elegant way, but it
worked
for me). [BTW you
can also use edit, goto, special to select cells with
formulas. Then right
click on the selection and make the changes. You would
have to do it
separately for every sheet though]
Good luck!

Bob L.


Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets

mysheet.Activate
Set myrange = mysheet.UsedRange

For Each mycell In myrange

Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty
cells

Case Else
'distinguish between constants and
formulas
If Left(CStr(mycell.Formula), 1) = "="
Then
mycell.Locked = True
Else
mycell.Locked = False
End If

End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub




.


Todd,
Your problem is that the sheets are protected so
cannot be locked. I
added a line of code to unprotect the sheets first, and
then protect them
after doing the locks.

Here it is:
Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets
mysheet.Unprotect
mysheet.Activate
Set myrange = mysheet.UsedRange

For Each mycell In myrange

Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty
cells

Case Else
'distinguish between constants and
formulas
If Left(CStr(mycell.Formula), 1) = "="
Then
mycell.Locked = True
Else
mycell.Locked = False
End If

End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub
Let me know if that takes care of it.

Bob L.


.

Todd,
The code needs to be in the same workbook as the sheets you want to
operate upon. Assuming you have that right, I don't know what the problem
could be. I did it in Excel 2000. I will email you the file, assuming the
post address is okay as is.

Bob L.