I've seen the postings related to protecting cells and worksheets and tried
to implement them on my workbook, but they're not working the way we would
like them to, so I'm posting my question in hopes of getting some ideas. We
have a workbook with a variety of worksheets. It is an expense tracking log
with each worksheet assigned to an individual. The manager then reviews this
workbook monthly. There are 6 columns, with the first 4 as data, the 5th as
the amounts column and has a sum function at the end. The 6th column is
where the manager will approve the row. My questions a

1) is there a way to protect the first 5 columns after the individual goes
to the next row or closes the log? We don't want them to go in and change
their entry before or after the manager reviews the data. As for the 6th
column, it will be locked after the manager signs off the rows. Typos and
errors have been discusses and will be dealt with accordingly.

2) will it be possible for the indivdual to insert rows as needed and will
the protection be carried over to the new rows?

3) is this applied to each worksheet specifically or can it be applied
globally to the entire workbook?

If I can clarify any of my questions, please let me know. Thank you for all
your help!

I don't know much about UserForms, but I think your situation might be helped
by this. Visit
http://j-walk.com/ss/excel/tips/userformtips.htm
and
http://www.contextures.com/xlUserForm01.html
to learn more.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Bowtie63" wrote:

I've seen the postings related to protecting cells and worksheets and tried
to implement them on my workbook, but they're not working the way we would
like them to, so I'm posting my question in hopes of getting some ideas. We
have a workbook with a variety of worksheets. It is an expense tracking log
with each worksheet assigned to an individual. The manager then reviews this
workbook monthly. There are 6 columns, with the first 4 as data, the 5th as
the amounts column and has a sum function at the end. The 6th column is
where the manager will approve the row. My questions a

1) is there a way to protect the first 5 columns after the individual goes
to the next row or closes the log? We don't want them to go in and change
their entry before or after the manager reviews the data. As for the 6th
column, it will be locked after the manager signs off the rows. Typos and
errors have been discusses and will be dealt with accordingly.

2) will it be possible for the indivdual to insert rows as needed and will
the protection be carried over to the new rows?

3) is this applied to each worksheet specifically or can it be applied
globally to the entire workbook?

If I can clarify any of my questions, please let me know. Thank you for all
your help!

Hi KC,
Thank you! The user form would be the best way to go as I suggested to the
business owners, but they prefer a workbook full of worksheets, one for each
individual. I might take your links and see if I can come up with a better
userform, but for now, I still prefer a solution to the worksheet style.
Thanks.

"KC Rippstein" wrote:

I don't know much about UserForms, but I think your situation might be helped
by this. Visit
http://j-walk.com/ss/excel/tips/userformtips.htm
and
http://www.contextures.com/xlUserForm01.html
to learn more.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Bowtie63" wrote:

I've seen the postings related to protecting cells and worksheets and tried
to implement them on my workbook, but they're not working the way we would
like them to, so I'm posting my question in hopes of getting some ideas. We
have a workbook with a variety of worksheets. It is an expense tracking log
with each worksheet assigned to an individual. The manager then reviews this
workbook monthly. There are 6 columns, with the first 4 as data, the 5th as
the amounts column and has a sum function at the end. The 6th column is
where the manager will approve the row. My questions a

1) is there a way to protect the first 5 columns after the individual goes
to the next row or closes the log? We don't want them to go in and change
their entry before or after the manager reviews the data. As for the 6th
column, it will be locked after the manager signs off the rows. Typos and
errors have been discusses and will be dealt with accordingly.

2) will it be possible for the indivdual to insert rows as needed and will
the protection be carried over to the new rows?

3) is this applied to each worksheet specifically or can it be applied
globally to the entire workbook?

If I can clarify any of my questions, please let me know. Thank you for all
your help!

My only other thought is to use macros. You'll have to make the worksheets
very hidden so the user is forced to enable macros, then you'd just create a
button on each worksheet whereby the manager clicks that button and types a
password to lock down completed rows.

If you use Excel 2003 or later, you could just use the Data|List command to
make an expandable data entry area that inherits the properties of the row
above it. It puts a blue asterisk under the current list as a placeholder
for starting a new record, much like a database. As new records (rows) are
added, the totals row moves down the page.

Naturally you'd want to protect your VBA project with a password, and you'd
want a workbook_beforeclose macro that sets the sheets to very hidden and
saves the file prior to closing.

Hopefully a professional will get to your question soon.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Bowtie63" wrote:

Hi KC,
Thank you! The user form would be the best way to go as I suggested to the
business owners, but they prefer a workbook full of worksheets, one for each
individual. I might take your links and see if I can come up with a better
userform, but for now, I still prefer a solution to the worksheet style.
Thanks.

"KC Rippstein" wrote:

I don't know much about UserForms, but I think your situation might be helped
by this. Visit
http://j-walk.com/ss/excel/tips/userformtips.htm
and
http://www.contextures.com/xlUserForm01.html
to learn more.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Bowtie63" wrote:

I've seen the postings related to protecting cells and worksheets and tried
to implement them on my workbook, but they're not working the way we would
like them to, so I'm posting my question in hopes of getting some ideas. We
have a workbook with a variety of worksheets. It is an expense tracking log
with each worksheet assigned to an individual. The manager then reviews this
workbook monthly. There are 6 columns, with the first 4 as data, the 5th as
the amounts column and has a sum function at the end. The 6th column is
where the manager will approve the row. My questions a

1) is there a way to protect the first 5 columns after the individual goes
to the next row or closes the log? We don't want them to go in and change
their entry before or after the manager reviews the data. As for the 6th
column, it will be locked after the manager signs off the rows. Typos and
errors have been discusses and will be dealt with accordingly.

2) will it be possible for the indivdual to insert rows as needed and will
the protection be carried over to the new rows?

3) is this applied to each worksheet specifically or can it be applied
globally to the entire workbook?

If I can clarify any of my questions, please let me know. Thank you for all
your help!

Thanks, KC! The process you described sounds like the one I'll need. I'll
keep looking until I hear more suggestions. Thank you!!

"KC Rippstein" wrote:

My only other thought is to use macros. You'll have to make the worksheets
very hidden so the user is forced to enable macros, then you'd just create a
button on each worksheet whereby the manager clicks that button and types a
password to lock down completed rows.

If you use Excel 2003 or later, you could just use the Data|List command to
make an expandable data entry area that inherits the properties of the row
above it. It puts a blue asterisk under the current list as a placeholder
for starting a new record, much like a database. As new records (rows) are
added, the totals row moves down the page.

Naturally you'd want to protect your VBA project with a password, and you'd
want a workbook_beforeclose macro that sets the sheets to very hidden and
saves the file prior to closing.

Hopefully a professional will get to your question soon.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Bowtie63" wrote:

Hi KC,
Thank you! The user form would be the best way to go as I suggested to the
business owners, but they prefer a workbook full of worksheets, one for each
individual. I might take your links and see if I can come up with a better
userform, but for now, I still prefer a solution to the worksheet style.
Thanks.

"KC Rippstein" wrote:

I don't know much about UserForms, but I think your situation might be helped
by this. Visit
http://j-walk.com/ss/excel/tips/userformtips.htm
and
http://www.contextures.com/xlUserForm01.html
to learn more.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Bowtie63" wrote:

I've seen the postings related to protecting cells and worksheets and tried
to implement them on my workbook, but they're not working the way we would
like them to, so I'm posting my question in hopes of getting some ideas. We
have a workbook with a variety of worksheets. It is an expense tracking log
with each worksheet assigned to an individual. The manager then reviews this
workbook monthly. There are 6 columns, with the first 4 as data, the 5th as
the amounts column and has a sum function at the end. The 6th column is
where the manager will approve the row. My questions a

1) is there a way to protect the first 5 columns after the individual goes
to the next row or closes the log? We don't want them to go in and change
their entry before or after the manager reviews the data. As for the 6th
column, it will be locked after the manager signs off the rows. Typos and
errors have been discusses and will be dealt with accordingly.

2) will it be possible for the indivdual to insert rows as needed and will
the protection be carried over to the new rows?

3) is this applied to each worksheet specifically or can it be applied
globally to the entire workbook?

If I can clarify any of my questions, please let me know. Thank you for all
your help!