Lock Cell range(Defined Names) but allow editing of cell content?

[Beyond_avarice]

Hey gang,

I have developed an excel template that my company can use to calculate and format their KPI stats. My company desired that the presentation include a header and footer that were Word specific and outputting to PDF. So originally this process involved copying the cells from the Excel file and paste as a picture to the Word doc; stretch and crop to size and save as PDF.

I have changed this process by re-creating the PDF layout with form fields and have designed the Excel template with Named ranges for each of the corresponding PDF form fields. I scripted the Excel template to export the Named ranges and respective values, to a Tab Delimited file; so that Acrobat can import into the correspondingly named form fields.

This is a contract position and so I need to keep everything as simple as possible for the next, average Accountant to be able to comprehend and take over the process.

Obstacle

I need to maintain the integrity of the Named cells. I do not want a Named cell to errantly be moved on top of another Named cell and effectively destroying that other Named cell. Acrobat requires that the import data have the first line (of the .txt) match the name of it's Form Fields and that the second line contain the data that will populate those fields. If a Named cell is effectively destroyed; then my script will not be able to identify the value that the Named cell was to contain; as the original Reference is destroyed.

I am trying to figure out a way to lock the Excel template range of cells from being moved around, but allow for the contents of these Named Cells to be manipulated. I am essentially trying to mimic Form Fields with just Excel cells, as the cells are easy to change data and formatting. Formatting in Acrobat requires at least the Standard package and knowledge of JavaScript and Regular Expressions. I need to keep this simple for the next average Accountant and that's why I need to make the original source of manipulation as the Excel template.

Locking the cells and protecting the sheet keeps them from being moved but also prevents changing the data. I need the cells locked into place but the contents open for manipulation. Yes, it is possible for the user to copy the data from the Excel template and one by one paste into each Form Field in Acrobat. But that is a lot of manual manipulation and I am looking at a similar project that would make that an unfeasible solution.

Thank you in advance.

 

[Roxxien]

Hello,

I might have the solution to your problem. I suggest you try to have auto-run macro that will activate when you open your workbook.
Those are the twos parameter in Excel that should solve your problem. Just to be sure, try it in a copy for your workbook.

Note : A workbook event macro should be in the workbook module (ThisWorkbook)

Sub workbook_open()
Application.CellDragAndDrop = False
Application.CopyObjectsWithCells = False
End Sub

If you follow this idea, you will need to reactivate those before closing your workbook.

Sub workbook_beforeclose()
Application.CellDragAndDrop = True
Application.CopyObjectsWithCells = True
End Sub

 

[Beyond_avarice]

Thank you Roxxien, This took me half way there.

I throw an error on the workbook_beforeClose procedure: "Compile error: Procedure declaration does not match description of event or procedure having the same name."

I have some other Addins like "GLWand" and I wonder if there is a conflict with this procedure.

 

[Roxxien]

I'm really not sure if your add-in can cause any problem because I don't have any to test with.

However, I searched on my side and it seem that the workbook_beforeclose required an argument

So i simply changed the second macro by this

Sub workbook_beforeclose(cancel as boolean)
Application.CellDragAndDrop = True
Application.CopyObjectsWithCells = True
End Sub

You should still test it in a copy.