Microsoft.Office.Interop.Excel VB.NET How to disable select locked values in a worksheet

By | February 12, 2018
Questions:

I am using the Microsoft.Office.Interop.Excel library in VB.Net on an excel sheet that is protected. All of the protected settings are working fine except that I cannot set Select Locked Cells which shows when you are using the Excel protect sheet interface but I cannot see how to set this to false using the library. I have tried all of the different values in the protect method but none of them toggle the value.

Excel protected sheet menu
Excel protected sheet menu

Below is the code snippet I am using to get my workSheet, populate some values, and then protect the sheet. GetWorkSheet is an internal method which will return a WorkSheet object, and invoiceData is a datatable which has the data which is being added to the spreadsheet.

Dim newSheet As Worksheet = getWorkSheet(newSheetName)

' Make the current Work Sheet active so that it will be accepting the data.
newSheet.Activate()
newSheet.Unprotect(sheetPassword)
For i As Integer = 1 To invoiceData.Rows(0).ItemArray.Count
    newSheet.Cells(transactionalRow, i) = invoiceData.Rows(0).ItemArray(i - 1)
Next
'work is done so protect it again
newSheet.Protect(sheetPassword, False, True, False, True, True, True, True, True, True, True, True, True, True, True, True)
excel.ActiveWorkbook.Save()
Answers:

It’s very confusing because that property is not set with the Protect statement.

After calling your protect statement, issue the following call:

ActiveSheet.EnableSelection = xlNoRestrictions

See this page for the other values for EnableSelection:
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.enableselection(VS.80).aspx

Questions:
Answers:

You can try recording a macro and setting it to see what kind of code it generates. I used to do that when I was working with the Interop libs.

Leave a Reply

Your email address will not be published. Required fields are marked *