Saving only specific cells in Excel

[Tactical]

For purposes of security, continuity and simplicity I would like to do the following on a workbook in which I've enabled security measures for both the underlying VBA and formulas.

1. Disable the File/Save option to prevent users from having multiple copies that would eventually become inaccessible as the original Excel workbook has date security features requiring users to maintain updates.

2. Allow users to save only cells that contain the user inputed values. The saved filename would be user entered.

3. Provide users with the ability to load saved data into the most up-to-date version of the workbook.

Any comments or direction much appreciated.

IRIS

 

[byundt]

IRIS,
How about moving all the calculation logic into a VBA add-in? The workbook would then just serve as a means to gather input data and display output. I've used this approach for engineering software, and it allowed me to deploy updates that applied to all the old data without altering the old workbooks.

What I'm imagining is for you to organize the inputs in a block (let's say in column A) with the outputs in a different block (let's say columns D:I). You would then select the output block and enter an array formula like:
=myUDF(InputBlock)
myUDF is a user defined function in the add-in.

You create the add-in using the File...Save As menu item. Just choose "Microsoft Office Excel add-in *.xla" as the "Save as type" bottom field in the dialog.

The add-in will contain all your VBA code and any worksheets that you haven't deleted. You won't be able to see the worksheets once the file has been converted to an add-in, but you can grab data from them. I use this latter feature to store data tables that aren't subject to change (pipe sizes, material physical properties, etc.). The user defined functions in the add-in then use this information to do their calculations.
Brad