Problem with protecting workbooks which have lists.

Hello

I originally posted this question at the bottom of a previous question, which I subsequently realised probably wasn’t a sensible thing to do, so I have posted the question afresh below. I hope I haven’t broken any forum rules be doing this.

I am developing a recording programme for moths, which I’m pretty near to completing now, but I have hit an issue which I can’t seem to sort out and any help would be appreciated.

The workbook contains four worksheets on which I am using tables to extend data, on two of the worksheets (‘Macro Moths’ and ‘Micro Moths’ worksheets). I want to protect certain areas of these two worksheets so that users don’t amend or delete any data. The data I wish to protect is linked to the ‘Index’ worksheet. However, once I protect the‘ Macro Moths’ and ‘Micro Moths’ worksheets, the tables on the no longer work, i.e. extend; can anyone help me please?

To make things a little easier I have attached the workbook and the following details below are exactly what I wish to achieve:-
1) On the ‘Macro Moths’ worksheet I want to protect columns A, B, C and E, but obviously for the table to extended when new data is added, rows have to be allowed to be inserted. Users will only be allowed to enter data in columns D and F:T.
2) On the ‘Micro Moths’ worksheet I want to protect columns A, B, C and D, but obviously for the table to extended when new data is added, rows have to be allowed to be inserted. Users will only be allowed to enter data in columns E:T.
3) I also want the complete ‘Index’ worksheet protected except for column G where there will be user input of data.
4) The ‘User notes’ worksheet can be totally protected.

To keep this recording programme as simple as possible I don’t wish to use any macros and/or any code. I am using Excel 2003.
Thanks, Hawkmoth1

Select all, right click, format cells, go to Protection tab, tick Locked.
Select column D (or whatever cell to be edited, right click, format cells, go to Protection tab, remove tick in "Locked".
Then protect sheet with password. While protecting sheet, tick Select unlocked cells.

Pls see the file attached. The sheet Micro Moths is protected (without password)

Thanks for this, but as regards the protection, this is exactly what I've been doing but once the protection has been set the list becomes deactivated - on the file you attached above you've actually applied the protection to the 'Macro Moth' worksheet and this has deactivated the list - any other suggestions with this issue please?

hawkmoth1

The problem I have here is that I intend to send this recording programme out to various recorders who have various degrees of competence, that's why I need to protect the various parts of the worksheet before I send it out to them.

The columns I want to protect are not pre filled with data, they are linked to data in columns on the ‘Index’ worksheet using formula e.g. . The data in the remaining columns F:T is inputted by the user as this would be their own recording information pertaining to each individual record.

Here is a little more information for you. Using the ‘Macro Moths’ worksheet as an example. When a species is added to the next empty row under the ‘Species Common Name’ the list expands and relevant most important data for that species is added automatically (from the ‘Index’ worksheet) to column A, B, C and E leaving the user to manually enter other less critical recording information on all the remaining cells in that row.

I hope this makes thing a little more clear.

Hawkmoth1

Ok, I can see what you’ve done here and yes your suggestion does work, so thanks for that, but I was really trying to avoid entering formula down the worksheets as this is why I decided to have the ‘list approach’ as the formula is only added to each row as each record is entered.

Apart from my point above the other issue I would have with your suggestion is that different recorders would use the programme entering vastly different amounts of records, therefore I wouldn’t possibly know how for down to pre load formula. Apart from that, pre loading a lot formula quickly increases the size of the file.

Hawkmoth1

Thanks Newdoverman, I really appreciate all your efforts, but as you’ve said, you are rather limited if you can’t test anything on Excel 2003. Do you have any other suggestion for a ‘workaround’ perhaps, or does anyone else have any thoughts or ideas regarding my issue.

Hawkmoth1

Thanks again Newdoverman, it’s really good of you to spend time considering my issue.

The data which users record is sent to me at the end of each calendar year and then I add it to a ‘master database’. This process can’t be done automatically as all data has to be checked and verified (my me) before it is manually added to the database.

Your idea of filling the formulae a long way and protecting the worksheet and then starting a new worksheet is not a bad idea and probably my best option at this time.

If you don’t mind I won’t actually tag this post as solved for a while, just in case someone else (or you) comes up with an alternative suggestion. Thanks again.

Hawkmoth1

Thanks for that suggestion - I am currently reconsidering all my options.

I am also wondering whether I could possibly come at this problem from a slightly different direction. Rather than generally protecting the columns I need protecting could I possibly just protect the formulae in the columns - do you think I would be able to use the 'list' in an active way then?

Hawkmoth1

Ok, thanks for confirming that protecting formulae won't help me.

The data in columns A, B, and C on both the Macro and Micro Moths worksheets is actually needed as it becomes part of the record which is stored on the master database.

Your last point isn't very practical as verifying and checking records is very time consuming, that's why it's done during the winter months, when there's more time on my hands, therefore it can only be done once a year.

I have had another idea, it might be a bit wild, but it's worth a mention - would this be possible to have a formula in the 'Species Common Name' column on the 'Macro Moths' worksheet and in the 'Species Latin Name' column on the worksheet which the user would automatically switch protection off when the cell is selected and switch it back on when the cell is deselected?

Thanks newdoverman - it looks like it's back to the drawing board then!

Hawkmoth1