I can't able to protect a sheet if it having any column / rows gro

[sudarsan]

Hi,

Please suggest is there any way out to protect the sheet, when the sheet is
having column grouping / row grouping.

I want users not to see the code, unless i protect the sheet it won't
happen.Hope there will be a solution for this problem.

Thanks in advance.

 

[ShaneDevenshire]

Hi,

The answer is probably, but I don't understand what you mean by column/row
grouping?

 

[Gord Dibben]

Enter this code in Thisworkbook module.

Private Sub Workbook_Open()
With Worksheets("Sheet1") 'adjust for your sheet name
.Protect Password:="justme", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Note; the outliing must be applied prior to saving and
re-opening.

To hide the code go to VBE and select Tools>VBA
Project>Properties>Protection>Lock Project For Viewing.

Supply a password.


Gord Dibben MS Excel MVP

 

[sudarsan]

Hi,

Thanks for the code, I don't know why sometimes it s not working.Do i need
run the macro everytime?

Thanks in advance.

 

[ShaneDevenshire]

Hi,

First, thanks Gord for interpreting the question.

The code Gord gave you must be added to the thisWorkbook_Open object and it
will automatically run.

Here is how you add the code he gave to your workbook:
0. Copy the code from the post and modify it by changing the password part
to whatever password you are using.
1. With the workbook open press Alt+F11
2. In the top left part of the screen the Project window is displayed with
your workbook and possilby others. You will see a line that reads VBAProject
(your file name.xls) under that is a folder called Microsoft Excel Objects,
open it if necessary so you can see something called ThisWorkbook.
3. Double click ThisWorkbook and on the screen on the right (empty white
area) paste in the code.
4. Click the Save button.
5. Close the VBA window, the screen you are in, returning you to Excel.

Everything else will take care of itself.

I suspect that you really don't need to protect the code, because the
average user will have no idea where to look or what to look for. But if you
need to follow the instructions for that before you save and close the VBA
window.


If any of this is helpful, please click the Yes button.