How to control multiple Sheets in the same Workbook

navic

Active Member
Joined
Jun 14, 2015
Messages
274
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Attachments

  • Untitled-1.png
    Untitled-1.png
    9.2 KB · Views: 12

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

navic

Active Member
Joined
Jun 14, 2015
Messages
274
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,033
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Jun 14, 2015
Messages
274
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,033
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jun 14, 2015
Messages
274
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,033
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jun 14, 2015
Messages
274
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,033
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,578
Members
410,853
Latest member
Tramp71
Top