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.

 

[Dalex]

Another quick question: what is the best way to do this on multiple sheets?

I've tried it a couple of ways and I'm not getting good results.

Dalex

 

[Juan Pablo González]

Another quick question: what is the best way to do this on multiple sheets?

I've tried it a couple of ways and I'm not getting good results.

Dalex

The easy is just to duplicate the code, from the line

With Sheets("Sheet1")

to the line

End With

 

[Dalex]

Your gonna think I'm mentally challenged, but I've tried that and I get a compile error.

Here is what it looks like:

Private Sub Workbook_Open()
With Sheets("Labor1"),("Expenses1")
With Sheets("Expenses1")
.Protect "test", , , , True
.EnableOutlining = True

End With
End Sub

 

[Juan Pablo González]

From the line.. till the line...

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

 

[Dalex]

Thanks again..it worked!!!!!!!!!!!!!!!!!!!!!!!

 

[Dalex]

Juan Pablo,

Would I be asking too much with the following:

Is it possible to allow an end user to freely change the name of a worksheet (after I write the code above), and still have the document password protected with the ability of expanding/collapsing column/rows?

Can I do thios without modifiying the code each time?

If you're not sure, then it's o.k. I'll work with what I've got.

Thanks again for all your help!

FYI - I've tested it and it appears negative.

 

[Juan Pablo González]

In cases like that you should use the codename of the sheet, which can only be changed in VBA...

In the VBE (Alt F11), go to the project explorer (Ctrl R).

In the folder for your workbook, you should see a subfolder called "Microsoft Excel Objects". In there appears the "ThisWorkbook" module, which is the one that we are using right now, and a module for each sheet that appears like this:

Sheet1 (Sheet1)

where the first Sheet1 is the codename, and the one in parenthesis is the one that appears in Excel.

With that, change the code from this:

With Sheets("Sheet1")

to

With Sheet1

and the same for the other sheets.

 

[Dalex]

I'm proud to say that I had thought of your solution, except that I was keeping the parenthesis.

Thank you Juan. My sincere gratitude.

Dalex

p.s. I may not be able to answer any of your excel questions, but if you ever need a word translated in Italian, feel free to e-mail me ..

 

[vix95]

Hi, If i use this code sometimes this is working and few times it is giving error

runtime error 1004 .. application defined or object defined error.


Private Sub Workbook_Open()
With Sheets("AMJ2.2")
.Protect "1", , , , True
.EnableOutlining = True
End With
End Sub

thanks

 

[Naddesche]

Dear Juan Pablo,

I thought your post would be the answer to all my problems - but after entering the code I keep receiving the error message "Compile error: Syntax error"... any idea what else I can do or what I'm doing wrong?

Thanks a ton in advance