How to set passwords?

I haven't ever done anything with passwords/protection in excel.

I have 2 workbooks. Workbook 1 is only one sheet. Workbook 2 has many sheets. They are used by two different groups and managed by a 3rd group. The 3rd group would like to remove themselves from the circle as what they do is very minor... they merely take information e-mailed to them from workbook 1 generated by a user form and they take that information and enter into a user form in workbook 2 that places the information elsewhere in book 2.

It seems like it would be really easy to take and add the two workbooks together to allow group #3 to not be involved.

One problem: Manager for group 1 doesn't want the Sheet#1 that displays at opening to be moved as it contains all the user forms used by them for the rest of the workbook and dosen't mind if that page is seen but doesn't want group 2 to be able to use that page.

Additional problem: Neither manager wants group 2 to be able to be able to see any of the other pages (3-15.)

My solution? Merge the two books by creating a new worksheet in workbook 2. Let both groups have the option to check this new workbook 2 out of sharepoint but to place two sets of passwords with-in the book. Password 1 would only allow users to use worksheet #2 and not "see" any other pages and Password 2 would allow users in other group to use page #1 as well as pages #3-15. Is anything like this possible??

Here is an example, when the workbook is opened All but sheet1 is hidden,
Enter the correct password and your sheet will Un-Hide. Macros must be enabled to be able to use the password UserForm

This is just the type of example I needed. Thanks so much!

I've gotten it to somewhat work ... have been playing around with it for awhile.

The problem I am having is that it doesn't seem to "require" you to enter passwords to see the sheets. I can merely right click on tab and choose unhide and it opens a list and I can choose any page I wish to see even without entering a password.

Is there an solution to this problem?

This has proven popular

PASSWORD FORM4.zip

Have you looked at my example?

Your Excel version changed OK

The other problem I am having is that I can enter either password and only the larger set of worksheets opens.

Please Login or Register  to view this content.

What's blocked? Click the link & a download page opens

I tried to change my opening code to the changes suggested above by doing this:

Please Login or Register  to view this content.

But I got an error code for the ending if without a beginning if

so I made the end if a comment but then I get the error code:

Run-time error '1004'

Method 'Visible' of object 'Sheets'failed

This is the same error code I was getting previously

It's been downloaded over 200 times from there. Are you on a works computer that might be blocking it?

Possibly your IT won't allow the download

Change Worksheets.Visible to ws.Visible and uncomment the End If:

Originally Posted by LiLi1

I tried to change my opening code to the changes suggested above by doing this:

Please Login or Register  to view this content.

But I got an error code for the ending if without a beginning if

so I made the end if a comment but then I get the error code:

Run-time error '1004'

Method 'Visible' of object 'Sheets'failed

This is the same error code I was getting previously

i.e.

Please Login or Register  to view this content.

EDIT:

Thought it might be worth mentioning:

Once you have got the VBA working you will need to lock the VBA Project for viewing. To do this:

In Visual Basic Editor:
Select Tools
Select VBAProject Properties
Select the Protection tab
Tick Lock project for viewing and type and confirm a password

pb71 thanks I altered the opening code to what you suggested and that runs fine without any error messages.

I also added the locked protection you suggested.

I still however have the problem where the passwords themselves don't seem to do anything ... I can enter 888 in the box or 111 or the actual "passwords" and the larger set of the sheets still open up. See code I posted earlier.

It doesn't seem to really matter what I typed in the box.... I can still also unhide any worksheet I want just by right clicking on the tab.

Is there any chance that you could post a copy of your workbook with any sensitive data removed?

EDIT:
With reference to your code above:

Please Login or Register  to view this content.

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

You can also remove:

Please Login or Register  to view this content.

...as worksheet A will always be visible

Yes pages and pages of VBA code ~10 sheets and ~12 modules of code and many user forms ~10 tied to vba code to e-mail, to SharePoint etc.

Sorry for the time delay.

Here's an example workbook... the suggestion to place "" marks around the passwords helped so that not any random typing opening the pages. However now nothing I enter works to unlock the pages..??

When I attach a document, save this message, open the message and attempt to open the file 1) I get a message about trusting links ( it shouldn't have any links?) and 2) if I choose enable it tells me the file is locked for editing by myself??
What am I doing wrong?

Thanks!!!!!
TextBox1 to Passwordentrybox makes so much sense....total newbie error, works much better now

Changed coding for sheets reference as well and works.

Took me a few attempts but seems to work great!