258
Views
9
Comments
[Advanced Excel] After Protecting Sheet Formatting Cells To Be Unlocked is Ignored
Question
advanced-excel
Service icon
Forge component by Carlos Freitas

Hi,

I am assuming I am setting this up correctly

My requirement is to lock the header row and block users from adding or removing columns.

I have protected the sheet and left the  allowInsertColumns and allowDeleteColumns as default(false)

set the other allows to true

used the CellFormat_ApplyToRange to set the locked state to false for row 2 column 1 to end of column/row

in the resulting spreadsheet all rows/columns are still locked

when I unprotect sheet and check protection they are in a locked state.

It seems the locked state of the format is being ignored


Same problem here, has anyone come up with a solution?

Hi,

I had the same problem while trying to protect the header of a worksheet.

The workaround used was uploading (as a resource) an excel sheet with the first row locked, and then fill the data in that worksheet, as shown in the oml attached.


Hope this helps,

Pedro

excel.oml

I have same problem, I cannot set a conditional lock to a cell.

Solution?

Having the same problem here... any solution? The workaround presented above only serves that specific use case. But, if we want to unlock some specific cells, it's no use.

Having same issue, not able to unlock specific cells. Did anyone find the solution?

 
MVP

Hi Neil

I haven't tried it personally, but have tried swopping around the order in which you are applying the logic to see if that gives a different result? I.e. first apply CellFormat_ApplyToRange and then do protect the sheet?

I'll set out some time this week to look into this one.

Hanno

Also have the same problem, I have to create a sheet where a user can edit parts of the screen and lock the rest of the workbook, anyone has a solution for this?

Kind regards,

Matthias

 
MVP

I believe this issue has been resolved in the latest version.

Hanno

Thank you, I am not in a position to test, if someone else is able to confirm this is fixed then I will mark as solution

Regards

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.