Excel 2010 VB Lock Cells

Not sure if this is what you are looking for but One way could be like this:

    ActiveSheet.Unprotect Password:="Lets_go"
    Range("A:ZZ").Locked = False
    Range("A:E,Y:Y").Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Lets_go"

Select allOpen in new window

the real challenge is to loop through the folder on my computer or network and lock and unlock  the appropriate cells in the  xls Workbooks

Modify this stripped-down code to suit your needs; I use this weekly to read through a folder of project scorecard files, collect data from named ranges in each, and then write a project summary.  Several references are included in the comments.  Line 45 can either call a sub with your lock and protection code, or you can replace the Call with a few lines of actual code.  If it's more than a few lines, though, I'd suggest a separate Sub.

NOTE: This sub requires the Reference file Windows Script Host Object Model, usually located at "C:\Windows\system32\wshom.ocx".

Option Explicit
Public Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
'     Changed the method of identifying the current directory using a UNC path.
'     https://social.technet.microsoft.com/Forums/office/en-US/33551b22-1a10-4c33-abd4-b96b8ec858f3/how-to-change-current-directory-to-a-network-drive-in-vba?forum=excel

Dim mobjFSO As IWshRuntimeLibrary.FileSystemObject          ' generic object for Windows Script Hosting Object Model
Dim mobjScorecardsFolder As Folder
Dim mcolFiles As Files
Dim miobjFile As Variant



Sub AaExamineFiles()
'This sub requires the Reference file Windows Script Host Object Model
'   Application.VBE.ActiveVBProject.References(4).FullPath = "C:\Windows\system32\wshom.ocx"
'   http://vbadud.blogspot.com/2008/04/get-references-of-vba-project.html

'Looping through files in a folder
'address the files collection
'    https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba
'address an individual file in the files collection
'    https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba

'----- create folder objects
    Set mobjFSO = CreateObject("Scripting.FileSystemObject")

'    ChDir gcstrFinalScorecardsWorkbookPath
    SetCurrentDirectoryA gcstrFinalScorecardsWorkbookPath  ' replaces ChDir for a network location
    Set mobjScorecardsFolder = mobjFSO.GetFolder(CurDir)


'----- loop through the files in folder U:\_Scorecards
    SetCurrentDirectoryA gcstrFinalScorecardsWorkbookPath
    For Each miobjFile In mcolFiles
        mstrScorecardWorkbookName = miobjFile.Name

        If Left(mstrScorecardWorkbookName, 1) <> "~" Then

            If Right(mstrScorecardWorkbookName, 5) = ".xlsx" _
            Or Right(mstrScorecardWorkbookName, 5) = ".xlsm" Then

                miintLoop = miintLoop + 1
                Debug.Print "Processing: " & mstrScorecardWorkbookName

                Call abLockAndProtect  ' THIS IS WHERE YOUR CODE GOES, or use the code from Wilder1626

            End If

        End If

    Next miobjFile


'----- housekeeping
    MsgBox "Processing is complete."

    Set mcolFiles = Nothing
    Set mobjFSO = Nothing
    Set mobjScorecardsFolder = Nothing
End Sub  ' AaExamineFiles

Select allOpen in new window

Compile error:
mobjFSO As IWshRuntimeLibrary.FileSystemObject

What library do I need to reference?

Use Tools/References from the VBA IDE menu, then click Browse.  Change the default Type Libraries selection to ActiveX Controls (*.ocx) and browse to "C:\Windows\system32\wshom.ocx".

Unrelated, anywhere I used the global variable gcstrFinalScorecardsWorkbookPath you can substitute hardcoding or reference your own variable to the folder where your files reside.

Post back if I can clarify anything for you.

I followed your instructions and get the same compile error

shieldsco: To get the 'FileSystemObject' you need the 'Microsoft Scripting Runtime' reference. This is available in all modern versions of Windows.

Then all you need to declare (but not instantiate) an object of the 'FileSystemObject' is the following:

Dim objFSO As FileSystemObject

Select allOpen in new window

After which at some point you need to instantiate as below:

Set objFSO = New FileSystemObject

Select allOpen in new window

I added the 'Microsoft Scripting Runtime' reference and the other two line of code but still get a compile error :user defined - type not defined



Option Explicit
Public Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
'     Changed the method of identifying the current directory using a UNC path.
'     https://social.technet.microsoft.com/Forums/office/en-US/33551b22-1a10-4c33-abd4-b96b8ec858f3/how-to-change-current-directory-to-a-network-drive-in-vba?forum=excel

Dim mobjFSO As IWshRuntimeLibrary.FileSystemObject          ' generic object for Windows Script Hosting Object Model
Dim mobjScorecardsFolder As Folder
Dim mcolFiles As Files
Dim miobjFile As Variant
Dim objFSO As FileSystemObject



Sub AaExamineFiles()
'This sub requires the Reference file Windows Script Host Object Model
'   Application.VBE.ActiveVBProject.References(4).FullPath = "C:\Windows\system32\wshom.ocx"
'   http://vbadud.blogspot.com/2008/04/get-references-of-vba-project.html

'Looping through files in a folder
'address the files collection
'    https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba
'address an individual file in the files collection
'    https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba

'----- create folder objects
    Set mobjFSO = CreateObject("Scripting.FileSystemObject")
    Set objFSO = New FileSystemObject

'    ChDir gcstrFinalScorecardsWorkbookPath
    SetCurrentDirectoryA gcstrFinalScorecardsWorkbookPath  ' replaces ChDir for a network location
    Set mobjScorecardsFolder = mobjFSO.GetFolder(CurDir)


'----- loop through the files in folder U:\_Scorecards
    SetCurrentDirectoryA gcstrFinalScorecardsWorkbookPath
    For Each miobjFile In mcolFiles
        mstrScorecardWorkbookName = miobjFile.Name

        If Left(mstrScorecardWorkbookName, 1) <> "~" Then

            If Right(mstrScorecardWorkbookName, 5) = ".xlsx" _
            Or Right(mstrScorecardWorkbookName, 5) = ".xlsm" Then

                miintLoop = miintLoop + 1
                Debug.Print "Processing: " & mstrScorecardWorkbookName

                Call abLockAndProtect  ' THIS IS WHERE YOUR CODE GOES, or use the code from Wilder1626

            End If

        End If

    Next miobjFile


'----- housekeeping
    MsgBox "Processing is complete."

    Set mcolFiles = Nothing
    Set mobjFSO = Nothing
    Set mobjScorecardsFolder = Nothing
End Sub  ' AaExamineFiles

I see the issue, the following line is not needed as VBA will think 'IWshRuntimeLibrary' is a user created object (unless you have a specific reference included for it):

Dim mobjFSO As IWshRuntimeLibrary.FileSystemObject

Select allOpen in new window

The following line...

Set mobjFSO = CreateObject("Scripting.FileSystemObject")

Select allOpen in new window

actually creates a generic object instance loaded with the FSO object.

What you need is to replace that line with:

Dim mobjFSO As Object

Select allOpen in new window

FWIW, I've attached a screenshot of my references; I did not use Microsoft Scripting Runtime (C:\Windows\System32\scrrun.dll), but instead used the Windows Script Host Object Model (C:\Windows\System32\wshom.ocx) and I get no compile errors.

Well yes, as I say; of you use that reference then you can then use the IWshRuntimeLibrary 'class' and you don't declare an 'Object' type of variable. But if you do use IWshRuntimeLibrary then it is not a 'generic' object as previously stated.

Basically you just need to match the declaration to the reference and not mix the two.

Compile error line: mobjFSO As IWshRuntimeLibrary.FileSystemObject

Now I get a compile error on line: gcstrFinalScorecardsWorkbookPath
Variable not defined

'Dim mobjFSO As IWshRuntimeLibrary.FileSystemObject          ' generic object for Windows Script Hosting Object Model
Dim mobjScorecardsFolder As Folder
Dim mcolFiles As Files
Dim miobjFile As Variant
Dim objFSO As FileSystemObject
Dim mobjFSO As Object


Sub AaExamineFiles()
'This sub requires the Reference file Windows Script Host Object Model
'   Application.VBE.ActiveVBProject.References(4).FullPath = "C:\Windows\system32\wshom.ocx"
'   http://vbadud.blogspot.com/2008/04/get-references-of-vba-project.html

'Looping through files in a folder
'address the files collection
'    https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba
'address an individual file in the files collection
'    https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba

'----- create folder objects
    'Set mobjFSO = CreateObject("Scripting.FileSystemObject")

    ChDir gcstrFinalScorecardsWorkbookPath
    SetCurrentDirectoryA gcstrFinalScorecardsWorkbookPath  ' replaces ChDir for a network location
    Set mobjScorecardsFolder = mobjFSO.GetFolder(CurDir)


'----- loop through the files in folder U:\_Scorecards
    SetCurrentDirectoryA gcstrFinalScorecardsWorkbookPath
    For Each miobjFile In mcolFiles
        mstrScorecardWorkbookName = miobjFile.Name

        If Left(mstrScorecardWorkbookName, 1) <> "~" Then

            If Right(mstrScorecardWorkbookName, 5) = ".xlsx" _
            Or Right(mstrScorecardWorkbookName, 5) = ".xlsm" Then

                miintLoop = miintLoop + 1
                Debug.Print "Processing: " & mstrScorecardWorkbookName

                Call abLockAndProtect  ' THIS IS WHERE YOUR CODE GOES, or use the code from Wilder1626

            End If

        End If

    Next miobjFile


'----- housekeeping
    MsgBox "Processing is complete."

    Set mcolFiles = Nothing
    Set mobjFSO = Nothing
    Set mobjScorecardsFolder = Nothing
End Sub  ' AaExamineFiles

is [gcstrFinalScorecardsWorkbookPath] a global variable that you have set outside this code ?

If not trying to change directories to an 'empty string' will cause errors. If it is set elsewhere - what is the value when it reaches this code ?

From my earlier post,

"Unrelated, anywhere I used the global variable gcstrFinalScorecardsWorkbookPath you can substitute hardcoding or reference your own variable to the folder where your files reside."

I made the following changes to my weekly procedure:

1) Removed the line
Dim mobjFSO As IWshRuntimeLibrary.FileSystemObject

2) Added the line
Dim mobjFSO As Object

3) Removed the Reference "Windows Script Host Object Model"
4) Added the Reference "Microsoft Scripting Runtime"


After those changes, my macro produces exactly the same results as before.

If I remove the Reference "Microsoft Scripting Runtime", I get the error "Cannot run the macro [my macro name]. The macro may not be available in this workbook or all macros may be disabled."

Restoring the Reference allows everything to function normally again.

I get the following error message:


Code:     For Each miobjFile In mcolFiles



Option Explicit
Public Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
'     Changed the method of identifying the current directory using a UNC path.
'     https://social.technet.microsoft.com/Forums/office/en-US/33551b22-1a10-4c33-abd4-b96b8ec858f3/how-to-change-current-directory-to-a-network-drive-in-vba?forum=excel

'Dim mobjFSO As IWshRuntimeLibrary.FileSystemObject          ' generic object for Windows Script Hosting Object Model
Dim mobjScorecardsFolder As Folder
Dim mcolFiles As Files
Dim miobjFile As Variant
Dim objFSO As FileSystemObject
Dim mstrScorecardWorkbookName As String
Dim miintLoop As String

Sub AaExamineFiles()
'This sub requires the Reference file Windows Script Host Object Model
'   Application.VBE.ActiveVBProject.References(4).FullPath = "C:\Windows\system32\wshom.ocx"
'   http://vbadud.blogspot.com/2008/04/get-references-of-vba-project.html

'Looping through files in a folder
'address the files collection
'    https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba
'address an individual file in the files collection
'    https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba

'----- create folder objects
    'Set mobjFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = New FileSystemObject
'    ChDir gcstrFinalScorecardsWorkbookPath
    SetCurrentDirectoryA "U:\Work Downlod Large\Policy Compliance\Data Call" 'gcstrFinalScorecardsWorkbookPath  ' replaces ChDir for a network location
    Set mobjScorecardsFolder = objFSO.GetFolder(CurDir)


'----- loop through the files in folder U:\_Scorecards
    SetCurrentDirectoryA "U:\Work Downlod Large\Policy Compliance\Data Call" 'gcstrFinalScorecardsWorkbookPath
    For Each miobjFile In mcolFiles
        mstrScorecardWorkbookName = miobjFile.Name

        If Left(mstrScorecardWorkbookName, 1) <> "~" Then

            If Right(mstrScorecardWorkbookName, 5) = ".xlsx" _
            Or Right(mstrScorecardWorkbookName, 5) = ".xlsm" Then

                miintLoop = miintLoop + 1
                Debug.Print "Processing: " & mstrScorecardWorkbookName

                'Call abLockAndProtect  ' THIS IS WHERE YOUR CODE GOES, or use the code from Wilder1626
               
    ActiveSheet.Unprotect Password:="Lets_go"
    Range("A:ZZ").Locked = False
    Range("A:E,Y:Y").Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Lets_go"


            End If

        End If

    Next miobjFile


'----- housekeeping
    MsgBox "Processing is complete."

    Set mcolFiles = Nothing
    'Set mobjFSO = Nothing
    Set mobjScorecardsFolder = Nothing
End Sub  ' AaExamineFiles

Change
Dim objFSO As FileSystemObject

to
Dim mobjFSO As FileSystemObject

because this is a module-level variable.

If you get an object error again, click Debug instead of End and post back with the variable name that is highlighted in yellow.

Error highlighted :For Each miobjFile In mcolFiles

Code:
Option Explicit
Public Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
'     Changed the method of identifying the current directory using a UNC path.
'     https://social.technet.microsoft.com/Forums/office/en-US/33551b22-1a10-4c33-abd4-b96b8ec858f3/how-to-change-current-directory-to-a-network-drive-in-vba?forum=excel

'Dim mobjFSO As IWshRuntimeLibrary.FileSystemObject          ' generic object for Windows Script Hosting Object Model
Dim mobjFSO As FileSystemObject
Dim mobjScorecardsFolder As Folder
Dim mcolFiles As Files
Dim miobjFile As Variant
Dim mstrScorecardWorkbookName As Workbook
Dim miintLoop As Integer



Sub AaExamineFiles()
'This sub requires the Reference file Windows Script Host Object Model
'   Application.VBE.ActiveVBProject.References(4).FullPath = "C:\Windows\system32\wshom.ocx"
'   http://vbadud.blogspot.com/2008/04/get-references-of-vba-project.html

'Looping through files in a folder
'address the files collection
'    https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba
'address an individual file in the files collection
'    https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba

'----- create folder objects
    Set mobjFSO = CreateObject("Scripting.FileSystemObject")

'    ChDir gcstrFinalScorecardsWorkbookPath
    SetCurrentDirectoryA "U:\Work Downlod Large\Policy Compliance\Data Call"  ' replaces ChDir for a network location
    Set mobjScorecardsFolder = mobjFSO.GetFolder(CurDir)


'----- loop through the files in folder U:\_Scorecards
    SetCurrentDirectoryA "U:\Work Downlod Large\Policy Compliance\Data Call"
    For Each miobjFile In mcolFiles
        mstrScorecardWorkbookName = miobjFile.Name

        If Left(mstrScorecardWorkbookName, 1) <> "~" Then

            If Right(mstrScorecardWorkbookName, 5) = ".xlsx" _
            Or Right(mstrScorecardWorkbookName, 5) = ".xlsm" Then

                miintLoop = miintLoop + 1
                Debug.Print "Processing: " & mstrScorecardWorkbookName

               ' Call abLockAndProtect  ' THIS IS WHERE YOUR CODE GOES, or use the code from Wilder1626
               
                    ActiveSheet.Unprotect Password:="Lets_go"
    Range("A:ZZ").Locked = False
    Range("A:E,Y:Y").Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Lets_go"


            End If

        End If

    Next miobjFile


'----- housekeeping
    MsgBox "Processing is complete."

    Set mcolFiles = Nothing
    Set mobjFSO = Nothing
    Set mobjScorecardsFolder = Nothing
End Sub  ' AaExamineFiles

I apologize for leaving out a statement; as I said, this is a stripped-down version of what I use.  I actually have a second loop ahead of the one shown here so I can work with a summary file someone provides me, so I need to check that it exists before I get started on processing the detail files.

Replace this:

'----- loop through the files in folder U:\_Scorecards
    SetCurrentDirectoryA "U:\Work Downlod Large\Policy Compliance\Data Call"
    For Each miobjFile In mcolFiles

Select allOpen in new window

with this (note the extra line)

'----- loop through the files in folder U:\_Scorecards
    SetCurrentDirectoryA "U:\Work Downlod Large\Policy Compliance\Data Call"
    Set mcolFiles = mobjScorecardsFolder.Files
    For Each miobjFile In mcolFiles

Select allOpen in new window

Post back and let me know what happens.

Error message in code:
 mstrScorecardWorkbookName = miobjFile.Name

Current Code:
Option Explicit
Public Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
'     Changed the method of identifying the current directory using a UNC path.
'     https://social.technet.microsoft.com/Forums/office/en-US/33551b22-1a10-4c33-abd4-b96b8ec858f3/how-to-change-current-directory-to-a-network-drive-in-vba?forum=excel

'Dim mobjFSO As IWshRuntimeLibrary.FileSystemObject          ' generic object for Windows Script Hosting Object Model
Dim mobjFSO As FileSystemObject
 Dim mobjScorecardsFolder As Folder
 Dim mcolFiles As Files
 Dim miobjFile As Variant
 Dim mstrScorecardWorkbookName As Workbook
 Dim miintLoop As Integer




Sub AaExamineFiles()
'This sub requires the Reference file Windows Script Host Object Model
'   Application.VBE.ActiveVBProject.References(4).FullPath = "C:\Windows\system32\wshom.ocx"
'   http://vbadud.blogspot.com/2008/04/get-references-of-vba-project.html

'Looping through files in a folder
'address the files collection
'    https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba
'address an individual file in the files collection
'    https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba

'----- create folder objects
    Set mobjFSO = CreateObject("Scripting.FileSystemObject")

'    ChDir gcstrFinalScorecardsWorkbookPath
    SetCurrentDirectoryA "C:\Users\shieldsco\Documents\Data Call"  ' replaces ChDir for a network location
    Set mobjScorecardsFolder = mobjFSO.GetFolder(CurDir)


'----- loop through the files in folder U:\_Scorecards
   SetCurrentDirectoryA "C:\Users\shieldsco\Documents\Data Call"
    Set mcolFiles = mobjScorecardsFolder.Files
    For Each miobjFile In mcolFiles

        mstrScorecardWorkbookName = miobjFile.Name

        If Left(mstrScorecardWorkbookName, 1) <> "~" Then

            If Right(mstrScorecardWorkbookName, 5) = ".xlsx" _
            Or Right(mstrScorecardWorkbookName, 5) = ".xlsm" Then

                miintLoop = miintLoop + 1
                Debug.Print "Processing: " & mstrScorecardWorkbookName

                'Call abLockAndProtect  ' THIS IS WHERE YOUR CODE GOES, or use the code from Wilder1626
               
                           ActiveSheet.Unprotect Password:="123"
     Range("A:ZZ").Locked = False
     Range("A:E,Y:Y").Locked = True
     ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123"
               

            End If

        End If

    Next miobjFile


'----- housekeeping
    MsgBox "Processing is complete."

    Set mcolFiles = Nothing
    Set mobjFSO = Nothing
    Set mobjScorecardsFolder = Nothing
End Sub  ' AaExamineFiles

Dim mstrScorecardWorkbookName As Workbook

Select allOpen in new window

Somehow the variable declaration was changed.  It needs to be a string (based on the prefix; m=modular variable, str=string type)

Dim mstrScorecardWorkbookName As String

Select allOpen in new window

Code runs but files are not locked

What do you mean when you say "the files are not locked"?  You should be able to open and view any file, but you shouldn't be able to edit the protected ranges you defined.

To test your protection statement I created a blank workbook:

A1="this cell is protected"
A2="this cell is unprotected"

I unchecked the Locked box on the Format Cells/Protection tab, then pasted your code into the immediate window of the IDE.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123"

Select allOpen in new window

That seemed to work correctly; I can edit cell A2, but an attempt to edit any other cell gives the expected message "The cell or chart you're trying to change is on a protected sheet."

No cells are protected.. can you copy the above code into a module and test?

Thanks so much.. excellent job

Thanks for your patience; sorry for all of the trouble.