How to control multiple Sheets in the same Workbook

[navic]

I am interested in an extended VBA code solution at this link How to tell if someone unprotected a worksheet (by author @Jaafar Tribak).

If I have multiple sheets. All Sheets contain some locked cells, Sheet is protected.
How to control multiple Sheets in the same Workbook (eg Sheet1, Sheet2, Sheet3).

I added two columns to the 'LogSheet', but I need VBA code that will return the data for those columns.
I addes two columns named 'Password' and "Sheet Name" (on the 'LogSheet' tab).
In which it will be seen on which Sheet the change was made and which password was entered by the user who re-locked the Sheet.

.......
.......
___________________________ Helper Routines ________________________________________________

Private Sub LogInfo(ByVal Status As PROTECTION_STATUS, ByVal SaveInfoToDisk As Boolean)

With Sheets(LOG_SHEET_NAME)
.Cells(1, 1) = "Protection Status"
.Cells(1, 2) = "User Name"
.Cells(1, 3) = "Time Stamp"
.Cells(1, 4) = "Entered Password"
.Cells(1, 5) = "Sheet Name"
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1) = IIf(Status = Protected, "Sheet Protected", "Sheet Unprotected")
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 1) = Environ("UserName")
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 2) = Format(Date, "Short Date") & " @ " & Format(Time, "Long Time")
'I need a VBA code that will display the last entered password ??????????????????
'I need VBA code for Sheet name where the password has been changed ??????????????????
.Columns("A:E").EntireColumn.AutoFit
.Range("A1:E1").Font.Bold = True
End With

If SaveInfoToDisk Then Me.Save

End Sub
.......
.......

Is it possible?

 

[navic]

I forgot this part of the VBA code, which goes at the beginning of the ThisWorkbook.

''''''''''''''''''''''''''''''''''
' Control Multiple Sheets
''''''''''''''''''''''''''''''''''
Private Enum PROTECTION_STATUS
Protected = 0
UnProtected = 1
End Enum

Private WithEvents Cmbrs As CommandBars

Private Const TARGET_SHEET_NAME = "Sheet1" '<= change target sheet name as required
'Private Const TARGET_SHEET_NAME = "Sheet2" '<= change target sheet name as required
'Private Const TARGET_SHEET_NAME = "Sheet3" '<= change target sheet name as required
Private Const LOG_SHEET_NAME = "LogSheet" '<= change log sheet name as required

 

[Jaafar Tribak]

In which it will be seen on which Sheet the change was made and which password was entered by the user who re-locked the Sheet.

I am afraid, what you are requesting goes against the forum rules so I can't help you with this.

 

[navic]

Hi @Jaafar Tribak
I do not understand,
Can you tell me which forum rules I am violating?
I asked a question and I put part of the VBA code.

I wanted to answer on that topic, but there the OP has a different request. (As far as I know, the rule of the forum is to stay out another question on thread that someone else started)

Anyway thank you for the answer.
I don’t know how else to ask the question.

 

[Jaafar Tribak]

Sorry, maybe I should have been clearer.

See Rule #6 .

I know your intention is legit but, providing code that reads a worksheet password behind the scenes can potentially be misused by an intruder to gain unauthorized access.

 

[navic]

Hi @Jaafar Tribak
OK, maybe I wasn't clear enough until the end (due to my insufficient knowledge of security in Excel)

I have one computer used by three users.
It contains an Excel workbook containing three worksheets.
Each user uses a worksheet with their name.
Each worksheet has its own password specific to its worksheet.
When a user wants to enter the required data, he opens his worksheet and unlocks the Sheet. Enters his calculation data. After enrollment he should lock the worksheet.

This way I want to prevent:
- that one user does not change the data of another user
- if the user (who knows the password, which I gave him), accidentally inadvertently changes and forgets it, I would then see which password he entered.

I apologize if this request of mine poses a security risk and in that case I give up the part related to the password.

Final question:
Is it possible to add only the 'Sheet' column (on the LogSheet) so that I know which sheet is in question and on which Sheet the last password change was made?
So, ignore password column on LogSheet)
Regards

 

[Jaafar Tribak]

Regarding your final question the answer is yes ... That could be done with code and woudn't viloate the forum rules.

Having said that, based on the scenario you described, It would probably be much better to simply not allow the users to re-protect the sheet once it has been unprotected and have some small code in the Workbook_BeforeClose to automatically protect back the sheet with the correct password upon closing the workbook . That way, there will be no risk of them accidentally setting the wrong password and forgetting it.

In other words, you allow the user to UnProtect the sheet but you don't allow them to Protect the sheet back.

In order to prevent the user from protecting the worksheet, the best thing that comes to mind is maybe to disable Sheet protection in the User Interface ie:= Disable the Protect tab in the ribbon as well the protect menu entry in the sheet tabs context menu.

I have little experience programming the ribbon but, if I get time I'll give it a try.

 

[navic]

Hi @Jaafar Tribak

Jaafar Tribak: I have little experience programming the ribbon but, if I get time I'll give it a try.

Thank you for wanting to help.

Can you on 'LogSheet', just add a 'SheetName' column, which has a re-entered password.
I've tried it but it doesn't work.

'_____________________________ Control Multiple Sheets ____________________________________
'______________________________________________________________________________________________

Private Enum PROTECTION_STATUS
Protected = 0
UnProtected = 1
End Enum

Private WithEvents Cmbrs As CommandBars

Private Const TARGET_SHEET_NAME = "Sheet1" '<= change target sheet name as required, passw 'aa'
'Private Const TARGET_SHEET_NAME_2 = "Sheet2" '<= change target sheet name as required, passw 'mm'
'Private Const TARGET_SHEET_NAME_3 = "Sheet3" '<= change target sheet name as required, passw 'qq'
Private Const LOG_SHEET_NAME = "LogSheet" '<= change log sheet name as required

'_______________________________ Monitoring Protection SetUp ______________________________

Private Sub Workbook_Activate()
EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME)) = True
' EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME_2)) = True
' EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME_3)) = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME)) = True
' EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME_2)) = True
' EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME_3)) = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME)) = False
' EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME_2)) = False
' EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME_3)) = False
End Sub

'__________________________________ PSEUDO-EVENTS _________________________________________

Private Sub OnSheetProtect(ByVal Sht As Worksheet)
LogInfo Status:=Protected, SaveInfoToDisk:=True
End Sub

Private Sub OnSheetUnProtect(ByVal Sht As Worksheet)
LogInfo Status:=UnProtected, SaveInfoToDisk:=True
End Sub

'_____________________________ Helper Routines ________________________________________________

Private Sub LogInfo(ByVal Status As PROTECTION_STATUS, ByVal SaveInfoToDisk As Boolean)

With Sheets(LOG_SHEET_NAME)
.Cells(1, 1) = "Protection Status"
.Cells(1, 2) = "User Name"
.Cells(1, 3) = "Time Stamp"
.Cells(1, 4) = "Sheet Name"
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1) = IIf(Status = Protected, "Sheet Protected", "Sheet Unprotected")
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 1) = Environ("UserName")
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 2) = Format(Date, "Short Date") & " @ " & Format(Time, "Long Time")
'I need VBA code for Sheet name where the password has been changed ??????????????????
.Columns("A:D").EntireColumn.AutoFit
.Range("A1:D1").Font.Bold = True
End With
If SaveInfoToDisk Then Me.Save
End Sub

Private Property Let EnableSheetProtectionMonitoring(ByVal Sht As Worksheet, ByVal Enable As Boolean)
If Enable Then
Set Cmbrs = Application.CommandBars
Else
Set Cmbrs = Nothing
End If
End Property

Private Sub Cmbrs_OnUpdate()
Static bPrevEnableState As Boolean
Dim bCurrentEnableState As Boolean

If ActiveSheet Is Worksheets(TARGET_SHEET_NAME) Then
bCurrentEnableState = Application.CommandBars.GetEnabledMso("Spelling")
If bCurrentEnableState And (bCurrentEnableState = Not bPrevEnableState) Then
Call OnSheetUnProtect(ActiveSheet)
End If
If bCurrentEnableState = False And (bCurrentEnableState = Not bPrevEnableState) Then
Call OnSheetProtect(ActiveSheet)
End If
bPrevEnableState = Application.CommandBars.GetEnabledMso("Spelling")
End If

' If ActiveSheet Is Worksheets(TARGET_SHEET_NAME_2) Then
' bCurrentEnableState = Application.CommandBars.GetEnabledMso("Spelling")
' If bCurrentEnableState And (bCurrentEnableState = Not bPrevEnableState) Then
' Call OnSheetUnProtect(ActiveSheet)
' End If
' If bCurrentEnableState = False And (bCurrentEnableState = Not bPrevEnableState) Then
' Call OnSheetProtect(ActiveSheet)
' End If
' bPrevEnableState = Application.CommandBars.GetEnabledMso("Spelling")
' End If
'
' If ActiveSheet Is Worksheets(TARGET_SHEET_NAME_3) Then
' bCurrentEnableState = Application.CommandBars.GetEnabledMso("Spelling")
' If bCurrentEnableState And (bCurrentEnableState = Not bPrevEnableState) Then
' Call OnSheetUnProtect(ActiveSheet)
' End If
' If bCurrentEnableState = False And (bCurrentEnableState = Not bPrevEnableState) Then
' Call OnSheetProtect(ActiveSheet)
' End If
' bPrevEnableState = Application.CommandBars.GetEnabledMso("Spelling")
' End If
End Sub

 

[Jaafar Tribak]

Hi @Jaafar Tribak

Thank you for wanting to help.

Can you on 'LogSheet', just add a 'SheetName' column, which has a re-entered password.
I've tried it but it doesn't work.

Sorry for the delay.

Add a new blank sheet to your workbook (name:= LogSheet) and place this code in the ThisWorkbook Module:

Option Explicit

Private Enum PROTECTION_STATUS
    Protected = 0
    UnProtected = 1
End Enum

Private WithEvents Cmbrs As CommandBars

Private Const LOG_SHEET_NAME = "LogSheet"      '<= change log sheet name as required


'__________________________________  Monitoring Protection SetUp ________________________________________________

Private Sub Workbook_Activate()
    EnableSheetProtectionMonitoring() = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        EnableSheetProtectionMonitoring() = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        EnableSheetProtectionMonitoring() = False
End Sub


'__________________________________  PSEUDO-EVENTS ________________________________________________

Private Sub OnSheetProtect(ByVal Sh As Worksheet)

    LogInfo Sh:=Sh, Status:=Protected, SaveInfoToDisk:=True

End Sub

Private Sub OnSheetUnProtect(ByVal Sh As Worksheet)
 
    LogInfo Sh:=Sh, Status:=UnProtected, SaveInfoToDisk:=True

End Sub


'__________________________________ Helper Routines ________________________________________________

Private Sub LogInfo(ByVal Sh As Worksheet, ByVal Status As PROTECTION_STATUS, ByVal SaveInfoToDisk As Boolean)

    If ActiveSheet Is Sheets(LOG_SHEET_NAME) Then Exit Sub

    With Sheets(LOG_SHEET_NAME)
        .Cells(1, 1) = "Sheet Name"
        .Cells(1, 2) = "Protection Status"
        .Cells(1, 3) = "User Name"
        .Cells(1, 4) = "Time Stamp"
        .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1) = Sh.Name
        .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 1) = IIf(Status = Protected, "Sheet Protected", "Sheet Unprotected")
        .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 2) = Environ("UserName")
        .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 3) = Format(Date, "Short Date") & " @ " & Format(Time, "Long Time")
        .Columns("A:F").EntireColumn.AutoFit
        .Range("A1:F1").Font.Bold = True
    End With
 
    If SaveInfoToDisk Then Me.Save

End Sub

Private Property Let EnableSheetProtectionMonitoring(ByVal Enable As Boolean)

    If Enable Then
        Set Cmbrs = Application.CommandBars
    Else
        Set Cmbrs = Nothing
    End If

End Property

Private Sub Cmbrs_OnUpdate()

    Static bPrevEnableState As Boolean
    Static oPrevSheet As Worksheet
   
    Dim bCurrentEnableState As Boolean
   
    bCurrentEnableState = Application.CommandBars.GetEnabledMso("Spelling")
   
    If oPrevSheet Is ActiveSheet Then
        If bCurrentEnableState And (bCurrentEnableState = Not bPrevEnableState) Then
            Call OnSheetUnProtect(ActiveSheet)
        End If
        If bCurrentEnableState = False And (bCurrentEnableState = Not bPrevEnableState) Then
            Call OnSheetProtect(ActiveSheet)
        End If
    End If
   
    bPrevEnableState = Application.CommandBars.GetEnabledMso("Spelling")
    Set oPrevSheet = ActiveSheet

End Sub

 

[navic]

Hi @Jaafar Tribak
Thank you very much. Everything works great.
Best regards