Protect - Unprotect Macro

All,

Attached is a macro I've been using for years, and would like to add:
1. Ability to use the 'data filters'
2. use the 'grouping' commands.

On various web pages, I've found syntax to perform additional steps 1 & 2. However, I've not been able to successfully compile them together.

Any help would be appreciated.

Protect-Unprotect.xlsm

Would you clarify what you mean please.

The filter drop down and grouping functionality are in place and work.

Arev you wanting to automate the appliation of some other grouping and to toggle the Autofilter?

Richard,

They are working, but the workbook is unprotected. Click the button, and then input any password to verify. My apologies.

Richard,

Did you have any further questions?

I'm still not clear what you're asking for.

Are you saying that with protection on you can't use the filter?

If so that's because when you set the password protection you need to tick the option that allows the filter to work

i.e. in VBA

Formula:

Please Login or Register  to view this content.

Yes sir that is correct. When I protect the document, I can not 1. filter (or) 2. use grouping.

I'd like to add this to the aforementioned VBA syntax; allowing filtering and grouping after protection.

Thanks in advance.

OK,
There are two aspects here.
1. Filtering has to be enabled when the sheet password is set.
2. Using the Grouping functionality is not allowed when the worksheet is protected.

To overcome #1 you should add that line I gave you last time to the CommandButton1 macro. i.e.

Please Login or Register  to view this content.

To overcome #2 you need to use some code in the Workbook Open event. i.e.

Please Login or Register  to view this content.

When a sheet is protected with UserInterfaceOnly in operation, the protection applies to stuff like keyboard clicks made by the user. VBA however is permitted to modify the worksheet as if there is no protection.
The UserInterfaceOnly setting isn't saved when you close the workbook, so you need to set it when the workbook is opened. The best place to do this is in the Workbook_Open event procedure.

Hope thsi helps.

Richard,

Thank you for your assistance. I've added the syntax to the CommandButton1. However when I run the macro it is now showing an error on the first line of Sub ShowPass() [indicated underline below]

Please Login or Register  to view this content.

Re your PM to me.

Please upload the workbook in the forum so that I may check.

Originally Posted by Kenneth Hobson

Your example file only has one sheet, not 2.

Please Login or Register  to view this content.

Hi Kenneth

IIRC there was a second hidden sheet.

Richard,

As requested, attached is my actual workbook I am having difficulties with..

What's the password?

I pm'd the password to you.

Kenneth was correct.

Although your previous workbook had two sheets - which prompted my response to Kenneth, this latest one only has one sheet.

Hence your code

Please Login or Register  to view this content.

which is looking for the second sheet tab fails.

You should avoide using code like Sheets(2) or Sheets("TimeLine").
The order of sheet tab names can easily be changed so that the sheet which VBA knows as Sheets(1) for instance will become Sheets(3) if it's tab name is moved to be the third sheet. Similarly sheet tab names are too easily changed so if you hard code these things you are creating a potential problem

Always use the VBA Sheet Code name. In this case Sheet1. This stays consistent whatever the user does in the Excel App.

Originally Posted by Richard Buttrey

Kenneth was correct.

Although your previous workbook had two sheets - which prompted my response to Kenneth, this latest one only has one sheet.

Hence your code

Please Login or Register  to view this content.

which is looking for the second sheet tab fails.

You should avoide using code like Sheets(2) or Sheets("TimeLine").
The order of sheet tab names can easily be changed so that the sheet which VBA knows as Sheets(1) for instance will become Sheets(3) if it's tab name is moved to be the third sheet. Similarly sheet tab names are too easily changed so if you hard code these things you are creating a potential problem

Always use the VBA Sheet Code name. In this case Sheet1. This stays consistent whatever the user does in the Excel App.

Thank you! I corrected the code to Sheet1.

However, now I am not able to use the grouping options...

Originally Posted by Kenneth Hobson

Your example file only has one sheet, not 2.

Please Login or Register  to view this content.

Kenneth,

Thank you sir!

When I protect the workbook now the filtering works great, however the grouping is inoperable. Any ideas?

Thank you sir. I will see what I can come up with and advise the group.

Hi,

Re your PM to me and comments here.

As I understand it the Grouping functionality will not work when Workbook Protection is in place.
I'll have a look further later.

Richard,

Thank you! This is beyond my skill set... (Yesterday I purchased the Excel 2016 Bible - specifically for the VBA section) Any other recommended reference publications?

Richard / Kenneth,

I was able to develop the syntax for a sub function while opening. This seems to work well. Is this the most efficient way to program the macro?

Please Login or Register  to view this content.

Well... I take that back. Saved, closed and reopened the workbook. It doesn't allow for grouping.

The search continues..

Not a definitive answer since I've not delved in again but I did notice yesterday that when I unlocked every cell, and I mean every cell, I seem to remember that allowed the Grouping functionality to work. But that might have been becuase the workbook was not protected.

Did you add the workbook protection to the last file you uploaded where it wasn't present before by any chance?

If that's the case and you still need to stop users selecting cells where there's a formula, then a SheetChange event macro that detected the use was selecting a formula cell could be used to stop them.

No sir, I did not upload a new revision of the document. Preferably, I only want users to input data in C6:G132 to ensure formulas and conditional formatting are not hampered with.

And be allowed to filter C6:G6, along with the grouping in the left pane.

Seem to have this coding issue resolved. Thanks for the support.