Excel Protect Properties

[iiyama]

Hi, I was hoping someone could assit me.  I'm try to protec an Excel sheet but with Select locked cells and Select unlocked cells enable as they are currently off.

I know how to procet a sheet but thats only with those options disabled.

I assume thats it's these two sets but I can't get the syntax right
 

xlNoRestrictions

To allow any cell to be selected

xlUnlockedCells

To allow selection of only unlocked cells

 

I was trying the below but it's not working and any combination I tried fails, so was hoping someone could help as I found nothing using search or online.

$oExcel.Activesheet.EnableSelection = xlNoSelection

 

Hopefully someone can help.  I created the code to unlock a group of cell and then lock a select set of cell it's just that I can't figure out the protec box properties.

Thanks for any help

Edited April 27, 2022 by iiyama

[Nine]

Please provide an example of the code that is not working.  Make it runable to us.  Use tags when you post code, as described.

[dmob]

If I understand you correctly, try this to keep C1 - C10 unlocked:

$oExcel.Range("C1:C10").Locked = False
With $oWorkbook.ActiveSheet
    ; set required options here
    ;.AllowFormattingCells = True
    ;.AllowInsertingColumns = False
    ;.AllowDeletingColumns = False
    
    .Protect("paswword")
EndWith

 

Edited April 25, 2022 by dmob
Correction

[iiyama]

Example code:

#include <Excel.au3>

Opt("TrayIconDebug" , 1)

$dir = 'C:\testdir\'
$file = 'test1'
$xls = '.xlsx'

$sFilePath = $dir & $file & $xls

Global $oExcel = _Excel_Open(Default, True, Default, Default, True)
Global $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath)


$oExcel.range("A1:K23").Locked = False; unlock

$oExcel.range("B2").Locked = True; lock
$oExcel.range("C14").Locked = True; lock
$oExcel.range("F23").Locked = True; lock

$oExcel.Sheets(1).protect('password')

Above is the example code I was using.

The issue seems to be that for the file/s I use and run the Protect Sheet option when manually opened Select locked cells and Select unlocked cells are defaulted to off.  If I make a new xlsx file these 2 options default to on and things work correct. Where I can click around the excel sheet I just then can't edit the locked cells but can any others. I was wanting a way to enable these 2 options on the xls files I use on the script above and be able to toggle them on using my code.

I adding previously >>> $oExcel.Activesheet.EnableSelection = xlNoSelection to no avail plus other attempts that failed.

The highlighted off need to be on using code.

I tried dmob's suggestion code: but with .Allow.SelectLockedCells = True and .Allow.SelectUnLockedCells = True but the same happened except the bottom 2 options Edit object and Edit scenarios de-select and then no items are ticked at all or if I leave .AllowFormattingCells = True or False the same are un ticked and none are selected.

Do you have any suggestions I could try or the correct syntax that I'm missing or ideas.

I'm using Autoit v3.3.14.5 I don't know if that makes any difference.

dmob's code:

$oExcel.Range("C1:C10").Locked = False
With $oWorkbook.ActiveSheet
    ; set required options here
    ;.AllowFormattingCells = True
    ;.AllowInsertingColumns = False
    ;.AllowDeletingColumns = False
    
    .Protect("paswword")
EndWith

Edited April 26, 2022 by iiyama