CODEWITHSUNDEEP
excelvba
Shorn Jacob
Shorn Jacob

Reputation: 1251

Protect one worksheet with a password from showing up

Is there a built-in way in Excel to protect just one spreadsheet in a Workbook with a Password?

Something like when user ticks or selects a Control , a password is prompted before a Worksheet become visible.

If its not built into Excel, can it be implemented with VBA?

Upvotes: 1

Views: 1036

Answers (1)

brettdj
brettdj

Reputation: 55692

You could try this approach which:

  • Makes the sheet VeryHidden and Protected so that it can't be unprotected - or detected - from the standard xl menus
  • Adds back in this protection on the Workbook_BeforeClose and Workbook_Open events
  • I haved used a sheet called YourSheet for this example
  • You should protect the VBA in this project as well to add further security.

Insert an Active X checkbox and use code to check if:

  1. The checkbox is True
  2. The user knows the password to UnHide the sheet. (Fred is used in this example)

CheckBox code

Private Sub CheckBox1_Click()
    Dim StrPass As String
    If CheckBox1.Value Then
        StrPass = Application.InputBox("Please enter the password", "Admin check")
        If StrPass = "fred" Then
            With ThisWorkbook.Sheets("YourSheet")
                .Unprotect "fred"
                .Visible = xlSheetVisible
                MsgBox "Sheet unhidden!", vbInformation
            End With
        Else
            MsgBox "wrong password", vbCritical
        End If
    End If
End Sub

the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With ThisWorkbook.Sheets("YourSheet")
        .Protect "fred"
        .Visible = xlVeryHidden
    End With
End Sub

Private Sub Workbook_Open()
    With ThisWorkbook.Sheets("YourSheet")
        .Protect "fred"
        .Visible = xlVeryHidden
    End With
End Sub

Upvotes: 2

Related Questions