CODEWITHSUNDEEP
excelvbams-accessworksheet
user3632179
user3632179

Reputation: 27

Access to Excel worksheet with a password

I created an Excel file with 3 worksheets like this:

enter image description here

My goal is: making the worksheets accessible only with a password. That means you can see the content of the worksheet only with a password. For Example: When the "User" clicks on "Admin", the content of the worksheet is only visible after entering the right password.

Worksheet protect is useless.

Is it possible ?

Upvotes: 0

Views: 564

Answers (3)

Pᴇʜ
Pᴇʜ

Reputation: 57683

There is no possibility to securely protect one sheet only from viewing. You can only protect a whole workbook from viewing (with password).

Any workaround you try to securely hide/protect a sheet with password can easily be tricked out by any user.

The only way to securely hide data from users is not to hand out this data at all. The only really secure way is to have something like a client server process where the server has the raw data, the client sends a request to that server, and the server only sends the data the user is allowed to see.

Upvotes: 1

ASH
ASH

Reputation: 20302

How about this ADO solution?!

Add Reference: Microsoft ActiveX Data Objects 2.8 Library

Sub test()

    Dim Conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dbPath As String
    Dim aQuery As String
    Dim pword As String
    Dim strcon As String


    dbPath = ThisWorkbook.Path & "\Database.mdb"
    pword = "abcd"
    aQuery = "SELECT * FROM myTable"

    strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=" & dbPath & ";" _
            & "Jet OLEDB:Database Password=" & pword & ";"

    Conn.Open strcon
    rs.Open aQuery, Conn

    If Not (rs.EOF And rs.BOF) Then
        MsgBox rs.Fields(0)
    End If

    rs.Close
    Set rs = Nothing
    Set Conn = Nothing

End Sub

Or, use a DAO solution.

Add Reference: Microsoft DAO 3.6 Object Library 

Sub test()

    Dim Conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dbPath As String
    Dim aQuery As String
    Dim pword As String
    Dim strcon As String


    dbPath = ThisWorkbook.Path & "\Database.mdb"
    pword = "abcd"
    aQuery = "SELECT * FROM myTable"

    strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=" & dbPath & ";" _
            & "Jet OLEDB:Database Password=" & pword & ";"

    Conn.Open strcon
    rs.Open aQuery, Conn

    If Not (rs.EOF And rs.BOF) Then
        MsgBox rs.Fields(0)
    End If

    rs.Close
    Set rs = Nothing
    Set Conn = Nothing

End Sub

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36870

Try following codes.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySheetName As String
MySheetName = "Admin1" 'The sheed name which you want to hide.
    If Application.ActiveSheet.Name = MySheetName Then
    Application.EnableEvents = False
    Application.ActiveSheet.Visible = False
    response = Application.InputBox("Password", "Enter Password", , Type:=2)
        If response = "rainy2019" Then 'Unhide Password.
            Application.Sheets(MySheetName).Visible = True
            Application.Sheets(MySheetName).Select
        End If
    End If
Application.Sheets(MySheetName).Visible = True
Application.EnableEvents = True
End Sub

VBA Window:

enter image description here

Upvotes: 0

Related Questions