VBA that locks worksheet with specific options checked

[Darkcloud617]

Hello. I have a pretty intricate VBA code that is working properly for my company. The point of the overall code is to open multiple locked workbooks, open all locked worksheets, make changes, lock the worksheets and lock the workbook. All password protected. The problem we are running into is that we are needing the 'lock worksheet' part of the code to only lock specific functions at this point. Essentially it needs to leave 'Select Unlocked Cells' and 'Format Columns' checked when it locks again (the user can modify columns). Right now it only leaves 'Select Unlocked Cells' checked.

It is a multi part code and I am just having issues getting this one thing altered for some reason. I think this is the part of the code that is handling the locking aspect. If you need to see the other parts (that arent private subs) let me know and I can include them.

Private Sub SetWorkbookAndWorkSheetProtection(wbk As Workbook, bProtected As Boolean)

    Const sPASSWORD As String = "Pass1"

    Dim wks         As Worksheet

    If bProtected = True Then
          wbk.Protect Password:=sPASSWORD
    Else: wbk.Unprotect Password:=sPASSWORD
    End If

    For Each wks In wbk.Worksheets

        If bProtected = True Then
              wks.Protect Password:=sPASSWORD
        Else: wks.Unprotect Password:=sPASSWORD
        End If

    Next wks

End Sub

The mainroutine sub may be responsible, so I am including that below also

Sub MainRoutine()

    Dim sProcessedWorkbooks As String
    Dim sMissingWorkbooks   As String
    Dim colWorkbooks        As Collection
    Dim wbk                 As Workbook

    Application.ScreenUpdating = False

'       Create a Collection of all of the workbooks which have been successfully opened
        Call OpenWorkbooks(colWorkbooks:=colWorkbooks, _
                           sProcessedWorkbooks:=sProcessedWorkbooks, _
                           sMissingWorkbooks:=sMissingWorkbooks)

'       Proceed only if the Collection contains at least one opened workbook
        If colWorkbooks.Count > 0 Then

'           Scan through each of the opened workbooks
            For Each wbk In colWorkbooks

'               Unprotect the workbook and each of its worksheets to allow modification
                Call SetWorkbookAndWorkSheetProtection(wbk:=wbk, bProtected:=False)

'                   Modify the workbook as required
                    Call ModifyWorkbook(wbk:=wbk)

'               Re-protect the workbook and each of its worksheets after modification
                Call SetWorkbookAndWorkSheetProtection(wbk:=wbk, bProtected:=True)

'               Save and close the workbook
                wbk.Close SaveChanges:=True

            Next wbk

        End If

    Application.ScreenUpdating = True

'   Display an appropriate closing message
    Call DisplayClosingMessage(sProcessedWorkbooks:=sProcessedWorkbooks, _
                               sMissingWorkbooks:=sMissingWorkbooks)
    
End Sub

Again, I am just needing to alter this code somehow to allow the user to alter the column formatting after this code locks the worksheet again.

Any help is greatly appreciated.

 

[Yongle]

Try recording various macros (similar to below) to generate the code you need and incorporate the relevant bits of recorded code into your code

record macro1
- unprotect the sheet
stop

record a macro2
- protect sheet with the 'Select Unlocked Cells' and 'Format Columns' checked
stop

run macro1 to unprotect the sheet

record macro3
- protect sheet with your total protection requirements set
stop

 

[Darkcloud617]

Thank you. I followed you advice and added this code AllowFormattingColumns:=True. Here is the string I added that to

Call SetWorkbookAndWorkSheetProtection(wbk:=wbk, bProtected:=True, AllowFormattingColumns:=True)

I get a Compile Error: Named Argument Not Found. Does a control like that need to be set somehow? I thought just adding it to the MainRoutine Sub may work as it calls in other parts of the code.

 

[Yongle]

If you want to do it like that then amend sub SetWorkbookAndWorkSheetProtection to deal with every possible protection option

SetWorkbookAndWorkSheetProtection(wbk As Workbook, bProtected As Boolean, include each protection option as an optional argument or use ParamArray )

ParamArray - see VBA Arrays - ParamArray

 

[Darkcloud617]

Thank you for the information. I didnt know that ParamArray function existed. I will get it running with that.

Thank you again.

 

[Yongle]

glad it helped