Hello,

Is it possible to lock/secure individual cells with VBA code so that
contents can't be changed? Specifically - formulas? Or am I restricted to
password protecting the entire workbook (read only mode)?

I would like to be able to enter data without having to unprotect the
workbook each time, but protect cells that contain formulas. Any advice
appreciated on the best way to secure my project with the least amount of
inconvenience.

Thanks,
Rich

each cell has a locked property. when the sheet is protected (not the
workbook), each cell who has it locked property selected is protected.

--
Regards,
Tom Ogilvy


"Rich" wrote:

Hello,

Is it possible to lock/secure individual cells with VBA code so that
contents can't be changed? Specifically - formulas? Or am I restricted to
password protecting the entire workbook (read only mode)?

I would like to be able to enter data without having to unprotect the
workbook each time, but protect cells that contain formulas. Any advice
appreciated on the best way to secure my project with the least amount of
inconvenience.

Thanks,
Rich

Thank you for your reply. I found the Lock property in Format Cells. But
when I add protection -- the Locked Cells and unlocked cells are checked by
default. I also noticed that all the cells are checked as locked by default.
Is there a setting somewhere to make the cells unlocked by default?

When I protect the sheet - I cannot edit any of the cells. How can I secure
only specified cells while I can still edit other cells in a given sheet?

Thank you.

"Tom Ogilvy" wrote:

each cell has a locked property. when the sheet is protected (not the
workbook), each cell who has it locked property selected is protected.

--
Regards,
Tom Ogilvy


"Rich" wrote:

Hello,

Is it possible to lock/secure individual cells with VBA code so that
contents can't be changed? Specifically - formulas? Or am I restricted to
password protecting the entire workbook (read only mode)?

I would like to be able to enter data without having to unprotect the
workbook each time, but protect cells that contain formulas. Any advice
appreciated on the best way to secure my project with the least amount of
inconvenience.

Thanks,
Rich

Actually, I think I am starting to get the hang of worksheet security. But I
am thinking that if I want to unlock a range of cells I could probably do
that in a VBA loop

dim rng as range, i as integer, j as integer
Set rng = range("C10:G100")
For = 1 to rng.rows.count
for j = 1 to rng.Columns.Count
rng(i,j).Locked = False
Next
Next


"Rich" wrote:

Thank you for your reply. I found the Lock property in Format Cells. But
when I add protection -- the Locked Cells and unlocked cells are checked by
default. I also noticed that all the cells are checked as locked by default.
Is there a setting somewhere to make the cells unlocked by default?

When I protect the sheet - I cannot edit any of the cells. How can I secure
only specified cells while I can still edit other cells in a given sheet?

Thank you.

"Tom Ogilvy" wrote:

each cell has a locked property. when the sheet is protected (not the
workbook), each cell who has it locked property selected is protected.

--
Regards,
Tom Ogilvy


"Rich" wrote:

Hello,

Is it possible to lock/secure individual cells with VBA code so that
contents can't be changed? Specifically - formulas? Or am I restricted to
password protecting the entire workbook (read only mode)?

I would like to be able to enter data without having to unprotect the
workbook each time, but protect cells that contain formulas. Any advice
appreciated on the best way to secure my project with the least amount of
inconvenience.

Thanks,
Rich

If doing it manually, click on the gray button at the intersection of the row
and column headers - this selects all the cells

then use format =cells to change the locked to unlocked. then select the
cells you want locked using the ctrl key if they are not contiguous and marke
them as locked
or do edit=goto=special and select formulas if it is the formulas you want
locked.

then repeat the format=cells.

in code

cells.locked = false
Range("A1,B9:B30,F15:Z35").Locked = True
or
Cells.locked = False
Range("C10:G100").Locked = True

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy


"Rich" wrote:

Actually, I think I am starting to get the hang of worksheet security. But I
am thinking that if I want to unlock a range of cells I could probably do
that in a VBA loop

dim rng as range, i as integer, j as integer
Set rng = range("C10:G100")
For = 1 to rng.rows.count
for j = 1 to rng.Columns.Count
rng(i,j).Locked = False
Next
Next


"Rich" wrote:

Thank you for your reply. I found the Lock property in Format Cells. But
when I add protection -- the Locked Cells and unlocked cells are checked by
default. I also noticed that all the cells are checked as locked by default.
Is there a setting somewhere to make the cells unlocked by default?

When I protect the sheet - I cannot edit any of the cells. How can I secure
only specified cells while I can still edit other cells in a given sheet?

Thank you.

"Tom Ogilvy" wrote:

each cell has a locked property. when the sheet is protected (not the
workbook), each cell who has it locked property selected is protected.

--
Regards,
Tom Ogilvy


"Rich" wrote:

Hello,

Is it possible to lock/secure individual cells with VBA code so that
contents can't be changed? Specifically - formulas? Or am I restricted to
password protecting the entire workbook (read only mode)?

I would like to be able to enter data without having to unprotect the
workbook each time, but protect cells that contain formulas. Any advice
appreciated on the best way to secure my project with the least amount of
inconvenience.

Thanks,
Rich

ActiveSheet.Range("C10:G100").Locked = False

To have all cells unlocked by default would require creating a Book.xlt Template
with that attribute.

Easiest just hit CTRL + a(twice in 2003) and format all cells to unlocked.

Then select those you want locked and format them.

Or vice versa


Gord Dibben MS Excel MVP

On Thu, 3 Jul 2008 10:49:01 -0700, Rich wrote:

Actually, I think I am starting to get the hang of worksheet security. But I
am thinking that if I want to unlock a range of cells I could probably do
that in a VBA loop

dim rng as range, i as integer, j as integer
Set rng = range("C10:G100")
For = 1 to rng.rows.count
for j = 1 to rng.Columns.Count
rng(i,j).Locked = False
Next
Next


"Rich" wrote:

Thank you for your reply. I found the Lock property in Format Cells. But
when I add protection -- the Locked Cells and unlocked cells are checked by
default. I also noticed that all the cells are checked as locked by default.
Is there a setting somewhere to make the cells unlocked by default?

When I protect the sheet - I cannot edit any of the cells. How can I secure
only specified cells while I can still edit other cells in a given sheet?

Thank you.

"Tom Ogilvy" wrote:

each cell has a locked property. when the sheet is protected (not the
workbook), each cell who has it locked property selected is protected.

--
Regards,
Tom Ogilvy


"Rich" wrote:

Hello,

Is it possible to lock/secure individual cells with VBA code so that
contents can't be changed? Specifically - formulas? Or am I restricted to
password protecting the entire workbook (read only mode)?

I would like to be able to enter data without having to unprotect the
workbook each time, but protect cells that contain formulas. Any advice
appreciated on the best way to secure my project with the least amount of
inconvenience.

Thanks,
Rich

Yes. I forgot about the gray button. Thank you for all the advice. I have
it under control, finally.


"Tom Ogilvy" wrote:

If doing it manually, click on the gray button at the intersection of the row
and column headers - this selects all the cells

then use format =cells to change the locked to unlocked. then select the
cells you want locked using the ctrl key if they are not contiguous and marke
them as locked
or do edit=goto=special and select formulas if it is the formulas you want
locked.

then repeat the format=cells.

in code

cells.locked = false
Range("A1,B9:B30,F15:Z35").Locked = True
or
Cells.locked = False
Range("C10:G100").Locked = True

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy


"Rich" wrote:

Actually, I think I am starting to get the hang of worksheet security. But I
am thinking that if I want to unlock a range of cells I could probably do
that in a VBA loop

dim rng as range, i as integer, j as integer
Set rng = range("C10:G100")
For = 1 to rng.rows.count
for j = 1 to rng.Columns.Count
rng(i,j).Locked = False
Next
Next


"Rich" wrote:

Thank you for your reply. I found the Lock property in Format Cells. But
when I add protection -- the Locked Cells and unlocked cells are checked by
default. I also noticed that all the cells are checked as locked by default.
Is there a setting somewhere to make the cells unlocked by default?

When I protect the sheet - I cannot edit any of the cells. How can I secure
only specified cells while I can still edit other cells in a given sheet?

Thank you.

"Tom Ogilvy" wrote:

each cell has a locked property. when the sheet is protected (not the
workbook), each cell who has it locked property selected is protected.

--
Regards,
Tom Ogilvy


"Rich" wrote:

Hello,

Is it possible to lock/secure individual cells with VBA code so that
contents can't be changed? Specifically - formulas? Or am I restricted to
password protecting the entire workbook (read only mode)?

I would like to be able to enter data without having to unprotect the
workbook each time, but protect cells that contain formulas. Any advice
appreciated on the best way to secure my project with the least amount of
inconvenience.

Thanks,
Rich