excelvba

Lock specific cells without knowing Sheet's name


I have 30 Workbooks with X Sheets that I don't know the names of.

I have a List of Workbooks like:

Each Workbook can have various Sheets with different names like:

Example1.xslx has sheets:

Example2.xslx has sheets:

Example3.xslx has sheets:

I did this code below:

sPwd = Trim(Range("A1").Value)
sLctn = Trim(Range("A2").Value)

For i = 21 To 61
        
    sVar1 = "B" + Trim(Str(i))
    If Range(sVar1).Value = "" Then GoTo Exit
    Sheet1 = Range(sVar1).Value
    sLctn = Trim(sLctn + "\" + Sheet1)
    Workbooks.Open Filename:=sLctn
    ActiveWorkbook.Unprotect Password:=sPwd
    
    For Each ws In Worksheets
        ws.Unprotect Password:=sPwd
        'I want to Lock a specific Range 
        ActiveSheet.Cells(3, 14).Locked = True
    Next ws
Next
Exit:

Excel said me that I can't set Locked properties for Range.

Do you have any idea how I can do it ?

I'm expecting to have Cells(3,14) locked that in these sheets are unlocked.


Solution

  • Instead of

    ActiveSheet.Cells(3, 14).Locked = True

    you should use

    ws.Cells(3, 14).Locked = True

    Looping over the worksheets in a workbook doesn't change the ActiveSheet - that will remain as whichever sheet was active when the workbook was opened.

    FYI it's more typical to use Exit For instead of (eg) GoTo Exit when you want to exit a For...Next loop