CODEWITHSUNDEEP
excelvbasecurityspreadsheet
Leedo
Leedo

Reputation: 611

Prevent hidden sheets in ThisWorkbook to be visible by using a macro and run from another workbook

My workbook has some very hidden sheets (xlSheetVeryHidden), because I do not want the end users to even see the info inside this sheets.
But, I surprised that using this method is very weak ,because any end user can run the below code from another workbook and see all the hidden sheets on the main file. 

Sub make_visible()
 
Dim wb As Workbook: Set wb = Workbooks("Test") ‘name of the main file
Dim sh As Worksheet
  For Each sh In wb.Sheets
  sh.Visible = xlSheetVisible
Next
 
End Sub

So, I need to isolated my workbook and prevent any macro found on other workbook to affect my main workbook.
Grateful for all useful comments and answer for this security issue.
Also,I locked my vba project , I have used a similar method like Unviewable+

Upvotes: 4

Views: 769

Answers (1)

Cristian Buse
Cristian Buse

Reputation: 4558

You could protect the workbook structure which would prevent the user to add/delete/hide/unhide sheets.

You can do it manually in the Excel interface in the Review ribbon tab:
screenshot

Or, you can do it programatically with something like:

'*******************************************************************************
'Protect the Structure of a given Book
'Returns TRUE if the Book is passworded with the provided pass otherwise FALSE
'*******************************************************************************
Public Function ProtectBookStructure(ByVal book As Workbook _
                                   , ByVal pass As String) As Boolean
    If book.ProtectStructure Then
        If UnprotectBookStructure(book, pass) Then
            'Previous password was the same or empty. Protect with new password
            ProtectBookStructure = ProtectBookStructure(book, pass)
        Else
            'Book is protected with a different password
            ProtectBookStructure = False
        End If
    Else
        On Error Resume Next
        book.Protect Password:=pass, Structure:=True
        On Error GoTo 0
        ProtectBookStructure = book.ProtectStructure
    End If
End Function

'*******************************************************************************
'Unprotect the Structure of a given Book
'Returns TRUE if successful or FALSE if not
'*******************************************************************************
Public Function UnprotectBookStructure(ByVal book As Workbook _
                                     , ByVal pass As String) As Boolean
    If book.ProtectStructure Then
        On Error Resume Next
        book.Unprotect pass
        On Error GoTo 0
    End If
    UnprotectBookStructure = Not book.ProtectStructure
End Function

The above methods can be called like:

ProtectBookStructure ThisWorkbook, "simplePassword"
UnprotectBookStructure ThisWorkbook, "simplePassword"

or you could make use of their return value to check if the protect/unprotect is succesful.

Edit #1

Having read the additional comments made under the OP's question, I must clarify a few things.

Excel files can only be made safe if you protect them via File/Info:
enter image description here

However, nobody can use such a protected file without the password. And once you give them the password then everything is accessible because:

  1. A Workbook protection, as the one I suggested in the initial answer under Review/Protect Workbook, can easily be 'cracked' by saving the file in the old .xls format and then simply removing it (no prompt for password). Same is applicable for Worksheets. Also, a brute force VBA macro could achive the same thing on older versions of Excel. Also, the internal files (under the Excel file archive) can be edited to remove the passwords
  2. The VBA project password can be cracked in at last 2 ways:
  • if opening the file with an archiver then one can edit the DPB=.. entry under \xl\vbaProject.bin\PROJECT
  • by using a macro to hook the password dialog via Win API

The suggestion I made in this answer only prevents 'normal' users to unhide the hidden worksheets but that does not stop more 'advanced' users to copy the information from those sheets via macros and most certainly does not prevent them to crack the passwords.

If you want total security on those hidden sheets then you should move the data outside of the Excel file as suggested by @SolarMike in the comments

Upvotes: 5

Related Questions