Darkcloud617
New Member
- Joined
- Sep 7, 2017
- Messages
- 38
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.
The mainroutine sub may be responsible, so I am including that below also
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.
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.
VBA Code:
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
VBA Code:
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.