Excel 2013 cell protection issue
Discussion
Also posted this on a LinkedIn group, but I thought I'd get a wider audience in here....
Apologies in advance for the lengthy post, but I thought I'd best give as much detail as I can.
For the last 15 years, I've been providing a small group of financial advisers with an Excel-based financial planning tool. It was originally written in Excel 97 and I've kept it up to speed with each new version (including the big changes for 2007 / 2010)
The system (which is called Clarity) has worked in Excel 2013 for the last couple of years, regardless of the O/S. However, three or four weeks ago, one (and ONLY one) of the worksheets is now opening with all the unprotected cells now protected. Entering the worksheet password doesn't even unprotect the sheet!
One of my subscribers has found that if he clicks around the worksheet quickly enough, it (very) occasionally allows an entry into a cell, but its hardly a solution!
On my "master" versions (I issue an xls and an xlsm version) the cells are unprotected. On any PC with Excel 2010 or earlier, the cells stay unprotected. Its just Office 2013 that seems to be arbitrarily applying protection (to the xls and xlsm versions), and even then, only to the one worksheet
I've even tried re-installing a version of Clarity from earlier this year (I issue monthly updates), but that is also affected, proving that its not something I've done, but something that has changed in Excel.
I've tried playing with most of the settings in Trust Centre, bit without any joy.
I currently have multiple, unrelated subscribers suffering the same issue, so its not a local PC problem.
I've been trying to get to the bottom of this for three weeks and having just spent two hours on a remote session for one of my subscribers, I'm on the verge of withdrawing support for Office 2013 users, which will clearly cost in terms of lost subscriptions.
Any thoughts?
Apologies in advance for the lengthy post, but I thought I'd best give as much detail as I can.
For the last 15 years, I've been providing a small group of financial advisers with an Excel-based financial planning tool. It was originally written in Excel 97 and I've kept it up to speed with each new version (including the big changes for 2007 / 2010)
The system (which is called Clarity) has worked in Excel 2013 for the last couple of years, regardless of the O/S. However, three or four weeks ago, one (and ONLY one) of the worksheets is now opening with all the unprotected cells now protected. Entering the worksheet password doesn't even unprotect the sheet!
One of my subscribers has found that if he clicks around the worksheet quickly enough, it (very) occasionally allows an entry into a cell, but its hardly a solution!
On my "master" versions (I issue an xls and an xlsm version) the cells are unprotected. On any PC with Excel 2010 or earlier, the cells stay unprotected. Its just Office 2013 that seems to be arbitrarily applying protection (to the xls and xlsm versions), and even then, only to the one worksheet
I've even tried re-installing a version of Clarity from earlier this year (I issue monthly updates), but that is also affected, proving that its not something I've done, but something that has changed in Excel.
I've tried playing with most of the settings in Trust Centre, bit without any joy.
I currently have multiple, unrelated subscribers suffering the same issue, so its not a local PC problem.
I've been trying to get to the bottom of this for three weeks and having just spent two hours on a remote session for one of my subscribers, I'm on the verge of withdrawing support for Office 2013 users, which will clearly cost in terms of lost subscriptions.
Any thoughts?
Can you create a new sheet to replace the role of the one which is locked - might get round the problem without having to understand it, or if the new sheet locks as well you know it's something in the data/set-up.
Also, Microsoft have a paid support service - https://support.microsoft.com/en-gb/gp/support-opt... - currently £200 for a single issue. Might be worthwhile if you're at risk of loosing paying customers.
Also, Microsoft have a paid support service - https://support.microsoft.com/en-gb/gp/support-opt... - currently £200 for a single issue. Might be worthwhile if you're at risk of loosing paying customers.
Are all of your subscribers affected on Office 2013?
If one isn't then check their exact Excel version (eg mine is 15.0.475.1003) - you may find a Windows/Office Update has stuffed something up. Not that common, but it does sound more like that if all your users on Office 2013 are affected.
Or, install (Maybe on a Virtual Machine) a fresh copy of Excel without the latest patches and see if it works
If one isn't then check their exact Excel version (eg mine is 15.0.475.1003) - you may find a Windows/Office Update has stuffed something up. Not that common, but it does sound more like that if all your users on Office 2013 are affected.
Or, install (Maybe on a Virtual Machine) a fresh copy of Excel without the latest patches and see if it works
@ LordGrover - has to be .xlsm in Excel 2007 or later, as the system runs lots of Macros and VBA
@ maffski - wasn't aware of the £200 per fix option at Microsoft - if I don't get to a solution in the next few days, I'll go for it.
@ nickd01 - I issued an email to all users last night to see if all 2013 users are affected. I don't have very many users now, but of the ones I know that use 2013, all are affected (including the IT support guy for one of them where we set up Clarity on a brand new virtual PC, just to test the issues).
@ maffski - wasn't aware of the £200 per fix option at Microsoft - if I don't get to a solution in the next few days, I'll go for it.
@ nickd01 - I issued an email to all users last night to see if all 2013 users are affected. I don't have very many users now, but of the ones I know that use 2013, all are affected (including the IT support guy for one of them where we set up Clarity on a brand new virtual PC, just to test the issues).
Just a few random ideas, as it does sound like an Excel as opposed to your workbook and these are more workbook based.
- Select all the cells in the sheet, and just unlock them all. Does that 'stick' ? Maybe then go through and start locking the cells you want locked
- Get Excel to highlight which cells it thinks are locked (Use conditional formatting)
- You've not switched from 32-bit to 64-bit by mistake?
- Try exporting all your VBA, remove it from the workbook & save, then re-import
- You're not including any add-ins's with your workbook by mistake?
If I can think of anything more constructive I'll let you know!
- Select all the cells in the sheet, and just unlock them all. Does that 'stick' ? Maybe then go through and start locking the cells you want locked
- Get Excel to highlight which cells it thinks are locked (Use conditional formatting)
- You've not switched from 32-bit to 64-bit by mistake?
- Try exporting all your VBA, remove it from the workbook & save, then re-import
- You're not including any add-ins's with your workbook by mistake?
If I can think of anything more constructive I'll let you know!
I can't even un-protect the worksheet - Excel 2013 simply won't allow it - I tried multiple times while on the remote support session last night and another (more tech-savvy) user also tried it with no luck
Its as if Excel has detected something on the page that it really doesn't like and is trying to protect the user from it.
Amusingly, the page in question is for recording the details of lump-sum investments. The next page is near-identical and is for recording regular premium contracts. A few differences, obviously, but to a casual observer, they look highly similar and have very close functionality and graphs, links etc
Its as if Excel has detected something on the page that it really doesn't like and is trying to protect the user from it.
Amusingly, the page in question is for recording the details of lump-sum investments. The next page is near-identical and is for recording regular premium contracts. A few differences, obviously, but to a casual observer, they look highly similar and have very close functionality and graphs, links etc
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff