Access to a hidden spreadsheet

Hi

Only way I know how to do it is Alt-F11 will get you into the VBA screen select your worksheet, bottom of the properties window you will see the word xlsheetvisible if you click once on that a small pulldown window will appear and you select xlsheetveryhidden, this will hide the sheet so that the normal hide/unhide in excel will not work. Unfortunatley VBA is beyond me at the moment hopefully a VBA expert will take this up for you.

Chris

http://www.excelforum.com/excel-prog...ith-macro.html

Hi

Found the above for you see if that helps.

Chris

In a standard module add this code:

Option Explicit

Sub HideDataSheet()
  Dim Answer$
  ' If Data sheet is visible then hide it
    If Sheets("Data").Visible = True Then
      ' Select the button
        ActiveSheet.Shapes("Button 1").Select
      ' Change the text on the button to reflect the status of the button
        Selection.Characters.Text = "Unhide Sheet"
      ' Hide the Data sheet
        Sheets("Data").Visible = xlVeryHidden
      ' Select cell A1
        Range("A1").Select
      ' Show a message that the sheet is hidden
        MsgBox "Data sheet is now hidden.", vbInformation
    Else
ResumeHere:
      ' If Data sheet is hidden then ask for a password
        Answer = InputBox("Password", "Please enter administrative password.")
        If Answer = "Hello" Then
          ' Unhide the data sheet if the password is correct
            Sheets("Data").Visible = xlSheetVisible
          ' Select the button
            ActiveSheet.Shapes("Button 1").Select
          ' Change the text on the button to reflect the status of the button
            Selection.Characters.Text = "Hide Sheet"
          ' Select cell A1
            Range("A1").Select
          ' Show a message that the sheet is hidden
            MsgBox "Data sheet is now visible.", vbInformation
        Else
          ' If nothing is entered or the InputBox is cancelled then exit the sub
            If Len(Trim(Answer)) = 0 Then Exit Sub
          ' If password is incorrect then show a message
            If MsgBox("Password is incorrect." & vbCrLf & "Would you like to try again?", vbOKCancel) = vbCancel Then Exit Sub Else GoTo ResumeHere
        End If
    End If
End Sub

Then add a button and link the button to this subroutine.
Don't forget to add a password to the vba project or anyone can just open and change the code.

It's a bit long but it checks several possible scenarios and gives message boxes along the way to guide the end user.

Hope this helps.

abousetta

You can hide the spreadsheet normally and then protect the workbook with a password (workbook, not worksheet). With the workbook protected, users cannot view hidden worksheets directly. Does that suffice?

Well if the OP does that then I just wasted the last 15 minutes coming up with this example

No, that is an activex button not a standard button (Developer >> Insert >> Form controls). I am uploading an example workbook. You many need to change the shape's name in the code depending on what it is called in your worksheet.

abousetta

Yes, you can. Here is a discussion about the differences I found online and here is the official bit from MS. If you want to use the ActiveX button, here is an example with both types of buttons.

Another question you might want ask yourself (or your managers):

* should the password be case sensitive

* do you want to show the inputted characters or show astrix instead (e.g. *****)

* how secure does this have to be... US Department of Defense level of security or average Jo security

Glad we are able to be of assistance. If you are satisfied with the answers then please mark the thread as solved.

Good luck.

abousetta



New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save