1

I made a workbook in which some sheets are needed to be protected from watching since a lot of people are going to enter this sheet and I would like to have more sensative information in it which will not be available for all.
To do so I googled and found out the following code: 

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySheets As String, Response As String
MySheet = "Sheet1"
If ActiveSheet.Name = MySheet Then
ActiveSheet.Visible = False
    Response = InputBox("Enter password to view sheet")
        If Response = "MyPass" Then
            Sheets(MySheet).Visible = True
            Application.EnableEvents = False
            Sheets(MySheet).Select
            Application.EnableEvents = True
        End If
End If
Sheets(MySheet).Visible = True
End Sub

which works perfectly for my needs but there is a problem.
The problem is as following, for it to work I need to force the user to enable macro commands and if you were to save the file then you'd have to save it as an macro-enabled workbook which is not something I trust my users to do.
My question is: Can I make so that without enabling the macro command you are unable to open the sheet without accepting the macros and you are unable to save as a normal Excel workbook?

Improve this question
10
  • Sheets(MySheet).Visible = True you're making your sheet visible in all cases? what is this code supposed to do? Commented Mar 28, 2017 at 4:42
  • What it does is that when ever someone tries to enter the sheet- the tab then it requests them of a password. If this password does not match the password which I wrote "MyPass" in this case then it doesn't allow them access. Commented Mar 28, 2017 at 4:47
  • 1
    This is what I dont understand, because after the If and before leaving the sub, you're doing again Sheets(MySheet).Visible = True. This statement is executed whatever password the users types in, did I miss something?? Commented Mar 28, 2017 at 4:53
  • The level of security your will achieve with that is not really that good. Anyway, are are ways to force your users to activate macros. You could, for example. hide all sheets except one that says "enable macros" and protect the workbook from changes. Then put some code in the "Workbook.Open" Sub that shows the Sheets. Commented Mar 28, 2017 at 6:41
  • I don't need it to be really that good I need users to not to accidently screw things up. That example is actually what I am currently trying to do, Do you have a good recommendation of what a good code for that might look like? The one I am currently using:[dummies.com/software/microsoft-office/excel/… which is giving me some problems and doesn't always work. Commented Mar 28, 2017 at 7:22

1 Answer 1

Reset to default
0

I think the best answer here is password-protecting that worksheet using excel's built in functionality: http://www.excel-easy.com/examples/protect-sheet.html

EDIT: protecting sheet only prevents edits, suggested new method of hiding detailed here: http://www.techrepublic.com/blog/microsoft-office/hide-excel-sheets-really-hide-them/

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This built in protect sheet won't work for me because you can still view the contents of a protected sheet. I need someone to not be able to go in
you are right. How about this sneaky trick? techrepublic.com/blog/microsoft-office/…
if I turn it into very hidden how do I then accsess it? I mean even the hyperlinks won't redirect me to it. Not to mention that the problem wasn't that I didn't want them not to write said page but not view the pages I want them not to be able to view

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.