Hide sheets, protect all sheets and workbook structure when closing

[Rasmusjc]

Hi all

I'm having a problem, and after many hours searching for a solution, i desidet to ask in here.

What im looking for is a macro which does:

When i close my workbook it should password protect some specific sheets, the workbook structure and then hide some of the sheets.

I simply can't find that solution.

Hope someone in here could help me.

I found this code but i can't get it to work.

PrivateSub Workbook_BeforeClose(Cancel AsBoolean)
Dim ws As Worksheet

ForEach ws In ThisWorkbook.Worksheets
If ws.Name = "HO"Or ws.Name = "BLs"Then
If ws.Visible <> xlSheetHidden Then ws.Visible = xlSheetHidden
ws.Protect "test"
Else
ws.Protect "test"
EndIf
Next ws

ThisWorkbook.Protect "chandoo", True

ThisWorkbook.Save

EndSub

 

[Logit]

.
This will hide the indicated sheets as well as provide a password to prevent data entry when visible:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim xWs As Worksheet
    Dim xName As String
    xName = "Sheet1"
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> xName Then
            xWs.Visible = xlSheetHidden
            xWs.Protect "test"
        End If
    Next
End Sub

Place this macro into a routine module for unhiding the hidden sheets :

Option Explicit


Sub SheetUnHidden()
    Dim xWs As Worksheet
    For Each xWs In Application.ActiveWorkbook.Worksheets
        xWs.Visible = True
    Next
End Sub

"Protecting" the entire workbook in essence is giving the same password to every sheet rather than
a separate action as you seem to have in your macro. You could add to the first macro provide above
to set a password for the first sheet as well upon workbook closing.

If you intent is to password protect the entire workbook, requiring the user to enter the correct password before
they can access any sheet, that would require a completely different and additional macro.

 

[Rasmusjc]

Thanks for the reply Logit

The code was one i found when i was looking for a solution, not that i worked.

The background is that: We are 20 people which have made a tipster club. Im running the account and i have a the info on 20 people (only name and phone) which only should be visible for me when i want it, and there are some info i use on other sheets.
Right now i have, sheet "members" - sheet "week1" - sheet "odds" and one sheet to copy/paste data.

When i close the workbook after i have copy/past the last data into "odds". I would like sheet "copy/paste" and sheet "members" to be (very)hidden and only visible with password.
The rest of the sheets should be visible, but not able to se any structure or edit.
For the workbook i would like to keep the same structure.

When opening i think i could place a button, which would unlock all the sheets, the workbook structure and make the hidden sheets visible again.

Right now i have a macro which hides the "member" sheet if i push a button, and types a password. If i push the same button it makes the sheet visible again i i type the password.
And then i have to type in a password when securing sheets and workbook, and again when i need to lock it up again.
A lot of typing password, so just need to find the best solution not to typing again and again.

Hope that was a better explanation than the fist post.

 

[Logit]

.
Paste this macro in the ThisWorkbook module :

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name = "Members" Or ws.Name = "Copy Paste Data" Then
            ws.Visible = xlSheetVeryHidden
        End If
    Next
    
    Sheet2.Protect ("pw2")
    Sheet3.Protect ("pw2")
    
End Sub

Paste this macro in a Routine Module :

Option Explicit


Sub HiddenSheetsShow()
Dim ws As Worksheet
Dim PassWord As String, i As Integer
  
  i = 0
Application.ScreenUpdating = False
Do
    i = i + 1
    
    If i > 3 Then
    
      MsgBox "Maximum 3 attempts complete." & vbCrLf & _
              "Workbook now closing.", vbOKOnly, "Password Fail"
        Application.DisplayAlerts = False
        ThisWorkbook.Saved = True
        Application.Visible = False
        Application.Quit
      Exit Sub
    End If
    
    MsgBox ("Attempt # " & i), vbOKOnly, "Password"
    PassWord = InputBox("Enter Password", "Maximum 3 Attempts")
    
Loop Until PassWord = "pw"
    
    If PassWord = "pw" Then
     
    
    Application.ScreenUpdating = False
    
    Sheet1.Visible = xlSheetVisible
    Sheet1.Activate
    Sheet1.Range("A1").Select
    
    Sheet4.Visible = xlSheetVisible
    Sheet4.Activate
    Sheet4.Range("A1").Select
       
    End If
    
Application.ScreenUpdating = True
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/CTTSgXq3tYqJM0mGU1TO3MWKaP38zpTsM8nGKQpvNTU

 

[Rasmusjc]

Thanks Logit

It works! Great idea with the "download workbook" it helped alot.

 

[Logit]

You are welcome.