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.
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