How to protect worksheet containing macros - tried, but losing functionality

I'm trying to protect the attached worksheet, but regardless of what combination of 'locking' cells and/or protecting the worksheet it has problems where the code - particularly H14 and I14, a data validation conditional list - doesn't function properly....

How can I protect the worksheet but still allow users to input data into row 14?

PS: created on excel 2003

Hi WillGe,

Try this.. 2012 Dose Report (windows).xls


I have protected the sheet with no password. So you unlock the sheet without a password.

Let me know if this is what you needed.


Thanks,

Bonny Tycoon

**If I was able to help please do not forget to click the small star icon at the bottom left of my post **

Hi Bonny,

For some reason protecting the worksheet knocks out the worksheet change code. This code applies to H14 & I14 - if you unprotect the sheet and click 'orthopaedics' in H14 then a list appears in I14, if you instead select 'Vascular' from H14 then a different list appears in I14. When the sheet is protected this function drops out and I have no idea why....

your on error resume next is hiding the reason.

Please Login or Register  to view this content.

Hi Joseph,

I'm not an expert at code by any means, do you have any suggestions how to fix the problem? I'm not sure what you mean by the 'error resume hiding the reason' except I guess that that line of code is causing the issue (I got the code through other forum users, didn't write it myself). Any ideas?

Originally Posted by WillGe

do you have any suggestions how to fix the problem?

use the code I just gave you ;-)

Hi Joseph,

Thanks for the code, only prob is I'm getting a runtime error when I use cells H14/I14, can you please have a go and see if you get the same error after protecting the worksheet. (I'm making sure the cells in row H are unlocked and then selecting protect worksheet under the Tools tab).

Error is:
Runtime error '1004':
The password you supplied is not correct....

The password is correct as I can unprotect the worksheet using it so why is it giving me this error?

so 'password' is the password then? (Didn't know that could be set in code like that)

Thanks I'll try that within a couple of days and let you know how I went !

Thanks Joseph, works nicely.

Can I ask another question of you? If I wanted to insert a calendar into the date cell B14 how can I do that? Preferably one that is not visible until selecting the cell or something, that you can then click on today's date (it will only ever be today's date anyway that people select).....

Using a click to add the date would be the best absolutely, how can I do that?

Hi Joseph, hoping you can get back to me with the click to add the date? Thanks!

Thanks Joseph!!!!!!