Locking Worksheets with a Macro

**dagindi** · 12-08-2009, 11:46 AM

Good morning,

I found the following code which allows you to lock sheets 1 to 3 with the ActiveX control buttons located on Sheet4.

Please Login or Register  to view this content.

I figured out how to change the code so that, lets say I want Sheet1 to stay visible.

Please Login or Register  to view this content.

What I cannot figure out is how to recode this so that I can setup the two ActiveX buttons on Sheet1 and it closes whatever sheet after it I want closed.

I am attaching the a sample spreadsheet I am working with and it has some additional notes on it.

Thanks!

**Leith Ross** · 12-08-2009, 02:37 PM

Hello dagindi,

Here is the code for the second button. It will prompt you for the sheet you want to hide. You can enter either the sheet number or name.

Macro to be placed in a VBA Module

Please Login or Register  to view this content.

Code for the buttons Click Event

Please Login or Register  to view this content.

**dagindi** · 12-08-2009, 04:39 PM

Leith,

Thanks for the recoding.

I believe I set up the coding per your instructions but it wont execute. I keep getting a syntax error.

What am I doing wrong?

**Leith Ross** · 12-08-2009, 06:42 PM

Hello dagindi,

I should have mentioned in my earlier post I don't have Excel 2007. You will need to save the workbook in 2003 format before I can view it.

**dagindi** · 12-08-2009, 08:15 PM

No worries.

Here you go!

**Leith Ross** · 12-08-2009, 09:21 PM

Hello dagindi,

Thanks for the new copy. I got the macros working and they have been added to the attached workbook. Here is the working code.

Please Login or Register  to view this content.

**dagindi** · 12-09-2009, 12:13 AM

This is working great and I really appreciate it.

I can't figure out how to remove the password request from the hidesheet function and apply it to the unhideshide function.

In other words a person would need the password to unhide the hidden sheets.

Thanks!

**Leith Ross** · 12-09-2009, 01:18 AM

Hello dagindi,

I changed the macro for the second button to ask for a password. Here is the code. This has been added to the attached workbook.

Please Login or Register  to view this content.

**dagindi** · 12-09-2009, 02:05 PM

Leith,

Thank you so much for all your help.

I was able to take what you provided and I tailored it to my exact needs.

Here is what I did:

Please Login or Register  to view this content.

This allows to lock/hide sheets without the need for password but wont allow another user to open the locked/hidden sheets without the password.

Again, thank you very much for all your help!!