Excel 2007 : Help with macro for password

[d3stroyah]

Hi everyone, i'd like your help to solve a problem i have.

I am no programmer but for work i need to set up an excel file with 4 sheets;

1-the first sheet must be protected with password and visible ONLY for who has the password.
2-opening the excel file the user (whoever he is) must see the sheet 2 as first sheet.
3-when the user clicks on the sheet 1 he can see an empty page (hidden all columns). He needs then to enter the password: if the password is correct the sheet 1 will appear properly, if not, it will stay locked with all columns hidden and no chance to modify it.
4 and main problem- i want to put the password once and never again until the excel file stays open. I had a macro that did the first 3 steps but i lost it and i had to re.enter the password every time i clicked on "sheet 1", which i don't want to do.
5-many people use this excel file, i need to hide the macro with the password for the sheet 1.

I work in an office, my job is not about solving this problem, i'm just an accountancy man, that's why i would really appreciate your help.

thanks!

edit: easier solution you could give me:
i need this behaviour:
i open the excel file and i can see sheet 2, 3 and 4, while sheet 1 is very hidden (or how you say it, i mean you can't even see it). I need a button on sheet 2 that if i press it i will need to enter a password. If the password is correct sheet 1 will be visible (and editable) until i close the excel file. After that i need to hide the VBA code for the button so that nobody can see what's the password behind it.
I think it's really easy for you, this second way.

[jeffreybrown]

Ciao 3stroyah,

Welcome to the Board,

Here is a start. In a regular module add this macro and then on Sheet2 add your button.

Sub Button1_Click()
    Dim MyPassword As String
    MyPassword = _
    Application.InputBox(prompt:="Enter password to protect worksheets/workbook", _
                         Title:="Password Input")
    If MyPassword <> "AA" Then Exit Sub
    With Sheet1
        .Unprotect MyPassword
        .Visible = xlSheetVisible
    End With
End Sub

In the ThisWorkbook module add

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet1.Visible = xlSheetVeryHidden
End Sub

Set the password for Sheet1, but as you see I have entered a password in the code already.

So nobody will know your password you can protect the VBA.

In the VBE >> Tools >> VBAProjectProperties >> Protection Tab

See how this works and then we can refine from here if needed.

[d3stroyah]

thanks a lot, it works exactly how expected.

[jeffreybrown]

You are very welcome

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

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

[d3stroyah]

i'm taking up the thread one more time. The solution you gave me is great, but they asked me if there's a way to show a "list of very hidden sheets" pressing the button, and then being able to choose which sheets i want to enable (password is the same for everything).

So, supposing i have 3 "private sheets" i'd like pressing the botton to have a list where i can chose to enable sheet1, 2, 3, or all of them, and then insert the password. Is that possible (maybe into an input box? I don't know, i'm just guessing)?

thanks!

[jeffreybrown]

This sounds possible and I will work something up as soon as I get a chance.

[royUK]

You need a userform with a ListBox to list the sheets. This example provides a list to start with.


SheetsToPrint.zip


Adapt the code that works on the selected sheets

[royUK]

You need a userform with a ListBox to list the sheets. This example provides a list to start with.


SheetsToPrint.zip


Adapt the code that works on the selected sheets

[d3stroyah]

haha you guys rock.

I took the example file and i adapted the code to hide/unhide sheets.

This is the working file for whoever could need it.

http://www.megaupload.com/?d=5TRPEJS1

now i can put [solved]!

thanks everyone!

[jeffreybrown]

Just wanted to pass along another example.

Glad you got it working.

[d3stroyah]

wow, that's efficiency . You must be someone really skilled and into it

[jeffreybrown]

Well the skilled part I am far from

I have just watched very intently here on this forum and others, but ultimately I am starting to understand how to modify some code here or there. Every once in awhile I get lucky.

Don't ask me to write anything from scatch because there are not enough hours in the day .