Hi
I have a tool set up that consists of 3 excel workbooks and 4 sharepoint repositories.
The first workbook has a number of macros that creates a user form to be completed, once completed and submitted the macro creates a pdf print of the form and saves it in one repository. It also writes all the information in the submitted form to a second "database" excel workbook.
The third excel workbook is simply a linked duplicate version of the database. This was created to ensure that I could open a version of the database without preventing anybody from using the tool that needs to write to the main database.
I would like to prevent any users apart from myself gaining access to the database however I need to grant contribute access to all by default to enable the tool to write to the database in the first instance. Presently I have the permission level set so users cannot delete the database and it is very discrete in that it's hidden in the site (not on navigation menus) and the database doesn't visibly open on the users screen when they use the tool.
Does anyone one have any ideas of how i can enable write acces to the database when only the macro in the first workbook writes to it and prevent all other means of view and read access I.e if someone happened to find the database?
The only possible method I can think at present is if I shaded the database such that the cells contents were not visible and could not be selected and then locked the sheet. A macro would then then be recorded in the sheet that writes to the database to unlock and then relock when a form had been submitted.
This seems a little crude though.
I have a tool set up that consists of 3 excel workbooks and 4 sharepoint repositories.
The first workbook has a number of macros that creates a user form to be completed, once completed and submitted the macro creates a pdf print of the form and saves it in one repository. It also writes all the information in the submitted form to a second "database" excel workbook.
The third excel workbook is simply a linked duplicate version of the database. This was created to ensure that I could open a version of the database without preventing anybody from using the tool that needs to write to the main database.
I would like to prevent any users apart from myself gaining access to the database however I need to grant contribute access to all by default to enable the tool to write to the database in the first instance. Presently I have the permission level set so users cannot delete the database and it is very discrete in that it's hidden in the site (not on navigation menus) and the database doesn't visibly open on the users screen when they use the tool.
Does anyone one have any ideas of how i can enable write acces to the database when only the macro in the first workbook writes to it and prevent all other means of view and read access I.e if someone happened to find the database?
The only possible method I can think at present is if I shaded the database such that the cells contents were not visible and could not be selected and then locked the sheet. A macro would then then be recorded in the sheet that writes to the database to unlock and then relock when a form had been submitted.
This seems a little crude though.
