Cell Protection over Excel 2000 and 2003
 

Good morning, All:

I have a scheduling workbook with several sheets that I created in
Excel 2003. The person who is to enter data into it is operating using
Excel 2000. The first three columns (A-C) have information that is to
be printed each day. After that, every four columns represents a
different day of the week. I've put protection on all of the cells
that contain formulas, and have set the permission for those cells to
include formatting, so that the person who enters data is able to hide
and reveal columns for printing purposes (e.g. he hides the data for
Monday when he wants to just print Tuesday). This kind of permission
works on Excel 2003, but when I went to check his settings on Excel
2000, there are only three options for permission: protect for
contents, objects and one other (can't remember the title).

Is there a way to do one of the following:
1. Write VBA code or a macro to allow him to hide the columns (i.e.
that overwrite the protection criteria in Excel 2000) but still protect
the formulas in the cells?
2. Write VBA code or a macro to hide the appropriate area depending on
the day (e.g. on Tuesday, print columns A-C and H-K, on Wednesday,
print columns A-C and J-M)?

Any help is greatly appreciated.

-Achez

Cell Protection over Excel 2000 and 2003
 

Since you've developed the workbook, you know the passwords for the worksheet.

You could record a macro that hides/unhides what you want.

Then modify that macro to:

unprotect the worksheet (with the password)
run your code
reprotect the worksheet (with the password)

Same kind of thing for #2.

Achez wrote:

Good morning, All:

I have a scheduling workbook with several sheets that I created in
Excel 2003. The person who is to enter data into it is operating using
Excel 2000. The first three columns (A-C) have information that is to
be printed each day. After that, every four columns represents a
different day of the week. I've put protection on all of the cells
that contain formulas, and have set the permission for those cells to
include formatting, so that the person who enters data is able to hide
and reveal columns for printing purposes (e.g. he hides the data for
Monday when he wants to just print Tuesday). This kind of permission
works on Excel 2003, but when I went to check his settings on Excel
2000, there are only three options for permission: protect for
contents, objects and one other (can't remember the title).

Is there a way to do one of the following:
1. Write VBA code or a macro to allow him to hide the columns (i.e.
that overwrite the protection criteria in Excel 2000) but still protect
the formulas in the cells?
2. Write VBA code or a macro to hide the appropriate area depending on
the day (e.g. on Tuesday, print columns A-C and H-K, on Wednesday,
print columns A-C and J-M)?

Any help is greatly appreciated.

-Achez

--

Dave Peterson

Cell Protection over Excel 2000 and 2003
 

Thank you, Dave, but unfortunately, that didn't work for me: when I ran
the macro, it prompted me for the password. Did I set the macro up
wrong?

Also, something that I didn't mention earlier is that the people
operating these spreadsheets are extremely low-level computer operators
- that's why I had to protect the sheet to begin with (the formulas
were being overwritten). So, maybe a better question, then, would be
is there any way to protect the contents of a worksheet in Excel 2000,
but still allow users to hide and reveal columns?

-Achez

Dave Peterson wrote:
Since you've developed the workbook, you know the passwords for the worksheet.

You could record a macro that hides/unhides what you want.

Then modify that macro to:

unprotect the worksheet (with the password)
run your code
reprotect the worksheet (with the password)

Same kind of thing for #2.

Dave Peterson

Cell Protection over Excel 2000 and 2003
 

You can supply the password in your code.

With worksheets("sheet9999")
.unprotect password:="topsecret"
'your code to do all the work
.protect password:="topsecret"
end with

I think that the developers of excel thought that it would be a security risk to
include the password when you record a macro.


Achez wrote:

Thank you, Dave, but unfortunately, that didn't work for me: when I ran
the macro, it prompted me for the password. Did I set the macro up
wrong?

Also, something that I didn't mention earlier is that the people
operating these spreadsheets are extremely low-level computer operators
- that's why I had to protect the sheet to begin with (the formulas
were being overwritten). So, maybe a better question, then, would be
is there any way to protect the contents of a worksheet in Excel 2000,
but still allow users to hide and reveal columns?

-Achez

Dave Peterson wrote:
Since you've developed the workbook, you know the passwords for the worksheet.

You could record a macro that hides/unhides what you want.

Then modify that macro to:

unprotect the worksheet (with the password)
run your code
reprotect the worksheet (with the password)

Same kind of thing for #2.

Dave Peterson

--

Dave Peterson