Forum Discussion
Excel - Access Table need to lock spreadsheet to prevent overriding values from Access used in Excel
I have an Excel spreadsheet were I have a Access connection to create a employee list. This values are used in several sheets and I dont want those values changed. If I lock the sheet I cannot refresh the data on that sheet if its locked. We may changes to the database adding and removing employees so I need to ensure the accuracy of the spreadsheet by refreshing the data from the Access table. On the sheet I refresh the connection I have a few fields that have formulas to update so those need to change but I dont want the user doing it and I dont want the user to add a employee to the sheet as then we will not have captured in the database that new entry. Any suggestions?
1 Reply
Steve-
I would create a macro in a locked VBA Project that:
1. unprotects the worksheet
2. Refreshes the data without overwriting the formulas
3. reprotects the worksheet.
Once the macro has been created. I would link it to a button on the worksheet. Let me know if you need some assistance, I can probably dig up some code from a past project.
