Remove error indicating unlocked cells are protected with VBA

Question

I have a template sheet that is copied through VBA into a new sheet.

The new sheet is added by clicking on a button that makes a userform pop up, and the information in the userform is transferred to the new sheet.

This new sheet contains locked headline cells as well as unlocked empty cells for the user to put information into. The sheet is protected but allows users to select locked and unlocked cells.

Everything worked for some months until the other day. When a new sheet is created and the user tries to fill in the unlocked cells, Excel responds as if they were locked!

The following information pops up:

"The cell or chart you're trying to change is on a protected sheet. To make changes, click Unprotect Sheet in the Review tab (you might need a password)."

This error disappears after the user does any of the following
1) double click in any empty cell (instead of just selecting it) and put in information this way and then click enter, then all cells behave normally.
2) activate any other worksheet and then return to this sheet
3) choose Format>Unprotect Sheet (this does not unprotect the sheet but it removes the error. If the user wants to unprotect the sheet then they have to click Format>Unprotect Sheet twice, i.e. first to remove this weird error and then to unprotect it).

I am trying, with VBA, to remove this error by doing any of 1, 2 or 3 above.

Any ideas how to solve this?

An additional problem is it is not possible to scroll with the mouse in the sheet while having the problem.

I tried the following code:

ActiveSheet.EnableOutlining = True 
ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True

Does this answer your question? How to unlock and lock Excel cells for running a macro — Tiago Martins Peres 李大仁

DSG DSG · Accepted Answer · 2016-03-04T16:04:18

After some googling I managed to solve this by doing the following:

Create a Module in VBA and add the following code:

Public Sub RefreshActiveCellSelection()
   ActiveCell.Select
End Sub

Whenever you perform a Workbook.Activate or Worksheet.Activate, immediately call

Application.OnTime Now, "RefreshActiveCellSelection"

Another solution was to uninstall KB3085502

I found the answer here: http://answers.microsoft.com/en-us/office/forum/office_2013_release-office_install/microsoft-update-excel-2013-kb3085502-32-bit/0cc7d498-92cb-4478-9554-77cff286c847

Remove error indicating unlocked cells are protected with VBA

1 Answers