How to adapt winnons macro for clearing cells to work when sheet is protected

I have attached a sample worksheet in which there is a macro that was written by Winnon for someone in another thread.
I have adapted it to suit my needs(only slightly as I don't know much about them) and it works really well, but when the sheet is protected, even though the particular cells aren't it won't work.

The cell range I want it to work on is A5 to H26 (which could change over time slightly) I don't want it to work anywhere else on the sheet, because everything else will be protected.
There is one added complication in that I would like to have column F5 : F26 within the range to be protected as well (but I could work round that if it is too difficult to achieve.)
So the problem is How to get the macro to work when the sheet is protected. Advice and help most appreciated
Thanks

It only seems to be me who is interested in this post but in the interest of learning I have attached my third sample file in which have somehow managed to get it fully working.
It now allows the cells to clear when the sheet is protected. I have surprised myself, but with trial and error I got there,
Unfortunately there seems to be one bug in the system which I don't have the skill to cure and another niggle that I would like to cure as well.
What it does correctly -
It allows me to delete the contents of the correct cells when the sheet is protected and then it leaves the rest of the cells as it was either protect or unptrotected.

What it does wrong -
If you nominate a row that it isn't allowed to delete, (row 27 onwards) it says something like 'this row is either not populated or is protected' you then click OK and it rightly doesn't delete the row.
This is good, BUT, it then unprotects the page, which is bad. I have obviously got the bit of code in the wrong place, but I've moved it all over the place and can't get it right, Can you please help me to place it properly?

Thanks, the password if needed is 1

Also, it will allow me to delete whatever is in rows 1,2 & 3 which I don't want it to. I just want it to be able to delete rows 5 :26

Thanks, I hope someone can help, you may not think so, but I have put a lot of effort into this, I haven't got that far, but I have got somewhere

Dear nje,

while unprotecting/protecting a worksheet to permit automated actions is a valid approach, there is an alternative which I think is slightly easier to implement.

There is a way of telling Excel that all currently existing protections should be in place for manual user actions only, in other words the user actions should be restricted but all VBA actions are allowed, even on a protected worksheet. This setting is not available from the menu and is also not saved with the workbook, in other words, the setting needs to be re-applied every time you open the workbook.

A simple way of doing this is to use the workbook_open event and add the following instruction

Please Login or Register  to view this content.

The above routine will be executed every time the workbook is opened. It will loop through all worksheets in the workbook and if it finds the worksheet to be protected it will add the UserInterfaceOnly parameter to permit all automated actions from VBA.

After you have added the above code, closed & re-opened the workbook, you can remove all unprotect/protect statements from your code as they will no longer be needed

nje, the code needs to be placed in the workbook module itself

Thanks OllieB
I've completely lost the plot now.
I have attached the workbook, but am back at square one to some degree.
I have put your code in the workbook module (thisworkbook) I removed the password bit from the Hide/Show which is in a module but that wouldn't work, so I put it into a 'sheet1' with the password bit of code and then it does work.

The delete content won't work now on the rows 5 to 26, which is where it is needed, but one good thing is, it doesn't unprotect the cells outside the range of 5:26 help!!

I'm sure it can all be resolved but it's getting a bit confusing now, but I do realise that it will be me who has messed this up, because I'm not really that sure what I'm doing

working on it - will revert shortly

and here it is - I have taken the liberty to re-write and change things around a bit - hope it is OK for you

New version attached. Row <= 4 is now also considered invalid

nje,

re post #12

I do not see any code on workbook level, other than the code I have added. I understand you want the focus to be on a specific worksheet when the workbook is opened. As that worksheet is not present in the sample workbook that I am working on for you, you will need to tell me the name of the worksheet

re post #12/#13
Your request was not complete 'Lastly, when I add sheets, will I need to....'

Thanks very much for being so helpful
My original workbook of which this is a sample contains this in the 'thisworkbook' module ' sub workbook_open() ("Master sheet") activate. end. so it always opens on the master sheet. and it's named 'Master sheet' all the other sheets are named 'Day 1' Day 2' through to 'Day 20'
Sorry about the confusion with the posts, but I kept getting locked out for some reason and couldn't finish typing.

nje, one more question. the 'clear row' and 'hide/unhide' buttons are present on every worksheet, or only on MASTER or only on Day*? (I need to know this for the protect UserInterfaceOnly part)

nje,

a new version attached.

- the userinterfaceonly settings will automatically be applied to all worksheets in the workbook that are protected - assuming the password is the SAME for all worksheets;
- after opening, the focus will be moved to a worksheet named "Master sheet"

and now with the new workbook

Re your question in post #17:

the 'clearing' of the row is performed by the code below

Please Login or Register  to view this content.

the most important step is

Please Login or Register  to view this content.

which sets a range of cells to be cleared by combining ('UNION') two separate ranges.

The first range consists of

Please Login or Register  to view this content.

Meaning the cells on the row specified by the user, from column A, and 5 columns in length - thus effectively from column A to E

The second range being joined is depicted by the below statement

Please Login or Register  to view this content.

a similar trick but now starting in column G and only 2 columns in length - thus effectively from column G to H

when you combine the two you have all the cells from A to H on the row selected by the user, with the exception of column F which is now your protected column. All subsequent actions (highlighting, clearing and removing the highlight) are performed against this range object (' rngClear').

If you are going to change the protected column, the only thing you need to change is the following statement using the explanation above

Please Login or Register  to view this content.

I hope I was able to explain this sufficiently

nje,

Something strange about determining if a worksheet is protected yes/no. The property does not always return the value I expected. I have changed the logic to look at the name of the worksheet. If it starts with Day, the protection property for VBA will be set. This should solve the problem.

I would personally move both buttons to the top section of the worksheets so they don't get affected by hide/I hide of the rows.