Expand/Collapse columns on password protected worksheets

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Apr 30, 2003

• #2

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

 

Upvote 0

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• Apr 30, 2003

• #3

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

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Apr 30, 2003

• #4

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>

 

Upvote 0

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• Apr 30, 2003

• #5

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

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Apr 30, 2003

• #6

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.

 

Upvote 0

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• Apr 30, 2003

• #7

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

 

Upvote 0

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• May 7, 2003

• #8

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.

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• May 7, 2003

• #9

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.

 

Upvote 0

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• May 7, 2003

• #10

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

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

Dalex

 

Upvote 0