Excel Forms Edit Only

Question

I have this excel sheets + a vba part with 3 forms that interacts with the sheets (read/write data, xml export, etc).

I have some data verification in my forms' code, so I would like to be able to modify values in the sheet only through the forms, not through direct "on-the-sheet" editing Is that possible?

When I lock the cells I want to protect, the forms cant modify the cells anymore either... in other words, is there a way to prevent editing, except through the macros/forms?

I am using excel2007 and might have to make it compatible for excel2003.

All help appreciated! :)

Answer 1

Maybe you can unlock the cells from the code just before modifying them, and then lock them down again after? Not very elegant I know. Maybe you could also hide the sheet from the user. I'm sure there is no perfect protection, because ultimately the user can turn VBA/Macros off. Question is how much you want to protect the data.

Answer 2

Is Microsoft Office Infopath available to you and your users as an alternative to Excel?

This is very specifically a "forms" application, with data validation and native XML data storage format so other apps can easily process them. You can use your own schema if you want. From your description, this sounds like what you want to accomplish.

Unfortunately, Infopath 2007 forms are not cross-compatible with Infopath 2003, so a mixed-version office (like mine) can be a showstopper.