Expand/Collapse columns on password protected worksheets

[Dalex]

Is there a way to password protect a document AND still allow for a user to expand or collapse columns?

Currently, it appear that when a worksheet is password protected, one cannot expand or collapse a column.

Thank you,

Dalex.

 

[Juan Pablo González]

You mean changing the column width ? no, not without a macro to "simulate" that... or use Excel XP.

 

[Dalex]

Thanks for response. I don't want to change the size of the columns, I just want to be able to click on the "plus" sign to expand them out. This doesn't change the column width..does it?

Also, I am currently on XP.

Thanks

 

[Juan Pablo González]

Right, I thought XP had that option in the protection, but not...

Ok then, let's use the same trick that works to enable the autofilter in previous versions. Put this in the Workbook module, and change the sheet (And password if you need to)

<font face=Courier New>
<SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> Workbook_Open()
    <SPAN style="color:darkblue">With</SPAN> Sheets("Sheet1")
        .Protect "password", , , , <SPAN style="color:darkblue">True</SPAN>
        .EnableOutlining = <SPAN style="color:darkblue">True</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

 

[Dalex]

Thanks,I'll give it a shot and let you know. I'm sort of new to entering code so if you can give me more baby steps I would be very thankful.

Dalex

 

[Juan Pablo González]

Look here for a small example:

http://www.mrexcel.com/tip038.shtml

but instead of inserting a new module, double click on the "ThisWorkbook" icon that you should see in the project explorer.

 

[Dalex]

Hello,

I typed in the formula in "This workbook" as you suggested, but it did not expand as expected. Please note that I am looking to expand rows and columns. Does this change the code? Also, am I to fill in the ", , , " part of the code with something?

Thank you

Dalex

 

[Dalex]

I've entered the code but am not getting the desired reults. Any additional thoughts.

Perhaps I'm not entering the code correctly.

FYI - I'm looking to do this to a worksheet and not a workbook.

 

[Juan Pablo González]

Ok, follow me on this steps.

Open Excel
Open the workbook that you want
Right click the little workbook icon that is on the LEFT of the File menu
Choose "View code" in there.
Paste the entire code that I gave you, just as it is.

The ONLY thing that you may need to change is the "Sheet1" with the name of the sheet that you want, and "password" with the password that you're using to protect the sheet.

After that, go back to Excel, save the workbook, close it, and open it again.

 

[Dalex]

It worked. Thank you. I guess I needed some hand holding.

Thank you, this capability makes my project great! You have helped metremendously!

Dalex