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

Beyond_avarice

Board Regular
Joined
Nov 13, 2012
Messages
195
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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)

Code:
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.
Code:
Sub workbook_beforeclose()
Application.CellDragAndDrop = True
Application.CopyObjectsWithCells = True
End Sub
 
Upvote 0
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.
 
Upvote 0
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

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

You should still test it in a copy.
 
Upvote 0

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top