Excel 2013 - Adjust Excel Sheet Protection options to allow users to make some changes

[aLi3nZ]

Hi There. I am needing to make an adjustment to the protection options in my excel workbook. I have set these options within the Microsoft Excel 2013 user interface, however - purhaps the macro I am running is overriding this.

I need users to be able to:

-Select Locked Cells
-Select Unlocked cells
-Format Cells
-Insert Rows

Could this be built into the existing macro as shown below? I have also attached a screen capture of the options set within Microsoft Excel Protect sheet. These options are set, however users are not able to make any of these changes after the Macro is run.


Capture.PNG

Please Login or Register  to view this content.

[cytop]

You need to reset the permissions when you re-protect the worksheet. Use the macro recorder to get the basic code...

[aLi3nZ]

Hi There,

Thank you, that makes sense. I used macro recorder and the following code was the result of setting these options:

Please Login or Register  to view this content.

Now if I modify my existing macro to include this, will this work? The formatting does look a bit strange, does this need to be changed?

Please Login or Register  to view this content.

[scottiex]

you may need the comma after the password

Please Login or Register  to view this content.

Also I note when you insert a row it will copy the protections form above.

[aLi3nZ]

Thanks so much. I will test out this updated Macro and let you know the results.

[aLi3nZ]

Hi There,

I have now tested the Macro and it is running perfectly across all Excel workbooks except 1.

1 Excel Workbook is giving a run time error as per below. Is there anything I can look at to prevent this from occuring?

Runtime.PNG

When selecting debug, it is selecting this:

Please Login or Register  to view this content.

Full code:

Please Login or Register  to view this content.

[scottiex]

Do you have merged cells?
If so they have properties like below

Please Login or Register  to view this content.

Although you should actually always avoid merged cells.

[aLi3nZ]

Thanks for your suggestion. I tried adding this code and testing, but there is still an issue. The latest run time error references this as the problem code:

Please Login or Register  to view this content.

[scottiex]

If it isn't merged cells you should probably upload the file and we can see what cell it fails on and what is the problem with it.

[aLi3nZ]

Hi There, yes that makes sense. Thank you very much. I have uploaded the spreadsheet that has the issue. I would really appreciate assistance with this. Macro is working well, just this 1 workbook doesn't like it.

[scottiex]

I means something like

Please Login or Register  to view this content.

But your sheet seems to have millions of rows in it in some sheets. You should delete the extra rows from every sheet and re-save it.

[aLi3nZ]

Thank you very much for pointing out this issue. I had not noticed this. I will be working through and deleting these and will let you know if it fixes the crashing.

[aLi3nZ]

Hi There,

I have gone through and cleared out these weird unused cells and everything is perfect. Thanks so much for all your support. I am marking this as solved.