Enable Draw Function in Protected Excel worksheet.

Occasional Visitor

Background

I am running office 365 and am working on an excel worksheet on a Microsoft Surface Go 2 tablet. I want to use a worksheet, daily, to record certain data (e.g., temperature).  Each day, once the data is entered, the user will initial beside the data they have entered (using the draw function).

To ensure data/signatures are not modified after entry, using a macro, excel locks cells containing data/signatures and password protects the worksheet. Blank cells, which will be used for future data/signature entry, remain unlocked.

 

Issue: Although data can subsequently be entered into unlocked cells, users cannot initial beside the data they have entered. From what I can tell, excel blocks drawing on a protected worksheet.

 

Questions:

  1. Can drawing be enabled on a protected worksheet?
  2. With respect to a protected worksheet, why would drawing disabled on unlocked cells? Is this a bug, or a feature?

When protecting the worksheet, I can allow the user to edit objects. This will allow them to sign beside data they have entered. However, it would also give them the ability to delete previous signatures (which would defeat the purpose of the signature).

 

Thanks for any help!

1 Reply
As for the drawing object and the unlocked/locked cells I believe they are unrelated. The drawing object is at the sheet level and just because that drawing object happens to be located over or even within the bounds of a single cell doesn't make it part of or contained within that cell.
As for locking a worksheet and the option to edit drawing objects or not, I suspect that is the best they can do.
As for what you could do is another issue. The best I could come up with is create a macro that runs before save/close and copies any new drawing object(s) (and cell values) to a secondary locked sheet.
So the way I see it could work is the main sheet is locked and a copy of the sheet is unlocked. The macro (which runs before a save event) compares the unlocked sheet with the locked sheet and then any new objects added (i.e. if they delete an object that doesn't matter, only if the object is on the unlocked sheet but not the locked sheet) get copied to the locked sheet. The delete the unlocked sheet and then make a new unlocked copy of the sheet for next time.