Err 1004 Hidden prop of range on protected sheet

[claven123]

I have a calendar on excel that after a certain date the user protects it. I've set up a macro that hides and unhides a group of cells (a planner) for the calendar. This allows the staff to see the calendar with just the dates and not all the planner stuff.

With the sheet protected I get the err 1004.... Unable to set the hidden property of the range class.

I found one solution to add the unprotect and then reprotect to the macro.... however, I don't have the password, the user does.

The other solution I just thought of was to set a print area to print..... does this have to be contiguous cells or can it be say... 4-5 groupings of cells?


Thanks,

Dennis

 

[claven123]

I guess I have two issues here. I have come to realise that it's not possible to print non continuous ranges on ONE sheet. So, that is out.

Any help on the first issue?

D

 

[mikerickson]

No, AFAIK there is no VBA way to defeat the password protection security feature.

 

[claven123]

I know, but I don't want to get at the password.

I need to be able to run the macro on locked/password protected cells/sheets. Any ideas?

The other option is to set he print areas to exclude the previously hidden cells. But, that has proven not to be a possibility.

Thanks,

Dennis

 

[mikerickson]

"to be able to run the macro on locked/password protected cells/sheets" is pretty much the definition of defeating password security.

One approach might be to have the planner a "private" feature and (to protect the user's privacy) require them to enter their password before altering it. You could either get the password from your own interface or let Excel's normal password dialog do the job.

The main thrust of my idea is that calling this a "privacy feature" makes a happier user than dictating to (informing) them "you have to enter your password."

 

[claven123]

Yes, yes you're correct. However, I never thought of it that way, but you're right.

I guess I'll have to come up with another way of doing it.

I have the calendar with the schedule on the first rows of the week then under that the planner for each person. This repeats for each week in the calendar... all on one sheet.

The macro hides the planner cells and the unhides them with the macro button.

They hide the planner cells when the schedule is finalized and move to the next month. Well, some want to see the planner to make trades during that month. However, they can't hide or unhide the cells with the macro, when it's protected.

Back to the drawing board.

Dennis