3

I searched about the said problem with various keywords but all results were either related to how to protect a workbook or unprotect it without knowing the password.

My current problem is not really a big problem but it feels so weird that I want to know if other people had/is experiencing it.

I am currently making Excel 2010 VBA Macros to automate data processing. I am adding workbook protection so that users cannot move the position of worksheets. I have done this via VBA 'thisworkbook.protect' or through the traditional Excel menu.

When I run the following code, the workbook protection completely disappears regardless of passwords, structure/window settings.

Sub test()
        ThisWorkbook.Worksheets(1).Cells(1, 1).Value(11) = ThisWorkbook.Worksheets(3).Cells(1, 1).Value(11)
End Sub

Just adding 'thisworkbook.protect' at the end of the code relocks the workbook and solves the problem, which is why this isn't a big problem. But the phenomenon is completely incomprehensible to me.

I appreciate anyone who would give feedback. Thanks.

edit 1 I believe this is not a worksheet.protect problem. My problem occurs regardless of whether worksheets and cells are protected, and I am specifically concerned about worksheet positions and names, which fall into the workbook.protect command.

Edit 2:
So the issue is in following and doesn't concern the difference between Workbook.Protect and Worksheet.Protect methods:
1. I open a protected workbook;
2. Run provided code snippet without unprotecting the workbook, as it doesn't affect worksheet's protection;
3. Workbook protection is cancelled by previous action despite my intentions. Have to protect the workbook again.

aynber
  • 22,380
  • 8
  • 50
  • 63
valefore
  • 31
  • 2
  • 1
    Well, it does, I can confirm this. It looks like a bug and it works in the newest Excel 2019 exactly the same. I mean this should be reportet to Microsoft as it breaks workbook protection completely. – Pᴇʜ Jan 31 '20 at 07:34
  • 1
    As I know, the `Workbook.Protect` Method protects the workbook from user interaction - overall workbook related actions (e.g. adding/deleting/moving sheets) **which are sent from UI**, but not from code. In order to protect sheets you have to use a `Worksheet.Protect` Method which works as it should for code raised changes as well. – Vitaliy Prushak Jan 31 '20 at 08:07
  • [Here](https://support.office.com/en-us/article/protect-a-workbook-7e365a4d-3e89-4616-84ca-1931257c1517) here is the source link for my comment. – Vitaliy Prushak Jan 31 '20 at 08:15
  • @Pᴇʜ Thanks for confirming. It's good to know I'm not the only one experiencing this problem. – valefore Jan 31 '20 at 08:45
  • 1
    @valefore Apologies, I’ve misunderstood the issue. I’ve deleted my answer as not relevant and suggested an edit to original post for issue to look clearer. – Vitaliy Prushak Jan 31 '20 at 09:42
  • 1
    @valefore I'm going to report this bug to Microsoft. – Pᴇʜ Jan 31 '20 at 12:30

1 Answers1

1

OK, the answer from Microsoft was this:

If I am interpreting your findings correctly, this report is predicated on the attacker having copied and accessed an Excel file that contained password protected workbook/sheets/fields that had not been encrypted. In this case this is an expected function.

My report was immediately closed so I had no chance to insist in this beeing a bug.

So they literally say it is a "feature" (expected function) not a bug. Obviously they consider the workbook protection not a security feature, the only secure protection is encrypting the workbook with a password that you actually need to enter before you can view it.

IMHO this is still a bug and a non expected behavior. But Microsoft obviously doesn't care which is very arguable since this bug means that workbook protection is useless and only stops users from accidentally destroying something.

So either you find a workaround since you know that you can re-protect it after you run that type of code, or you don't use that "feature" causing the bug.

But still anyone who knows about this bug, can use it to remove the workbook protection from any workbook. I see no way to prevent the removal. And since this bug is already working in Excel 2010 as well as in recent versions it is very unlikely that Microsoft will fix that in the near future.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73