password to open a work sheet in a workbook

[desduf]

I have 4 work sheets in a workbook. Worksheet 2 is hidden.
using a macro I want to put a button in work sheet 1 with a password to
enable to unhide and view work sheet 2. New to VOB Any help please.

 

[Kassie]

Firstly, you do not need to unprotect a hidden sheet, to unhide it, nor do
you need to unprotect a visible sheet to hide it.

Secondly, do you want to view it, or work in it?

Insert the button in Sheet 1. Right click, and click on Properties. Set
the caption as Display Sheet 2.
Again, right click on the button, and then on View Code.
Directly under CommandButton_1.Click(), and above End Sub, insert the
following code
Sheets("Sheet2").visible = true will show it

Insert a second button, set the caption to Hide Sheet 2
Insert the following line in the code area
Sheets("Sheet2").visible = xlVeryHidden will hide it.
Using xlHidden will hide it so that you can unhide it without using a macro.

To unprotect/protect a sheet, use
Sheets("Sheet2").unprotect ("your password") or
Sheets("Sheet2").protect ("your password")

However, if a sheet is xlVeryHidden, it makes no sense to protect it, only
to unprotect it when making it visible,

--
HTH

Kassie

Replace xxx with hotmail

 

[Hannah Lu]

Insert a button on Sheet1 (any shape, I prefer the Bevel button). Title the
button "Hide/Show Sheet2".
Copy the button and paste it (preferably in about the same location) on
Sheet2.

Press ALT+F11 to open up Visual Basic Editor.
Right click on your project - usually named VBAProject(worksheetnamehere).
Select "Insert", then "Module".
Double click on the new module to open it.

In the module, paste the code below (from the word "Sub" to "End Sub").
Then switch back to your worksheet, right click on the button on Sheet1 and
select "Assign Macro..." and select "ShowHideSheet2" from the popup window.
Repeat for Sheet2.

Now when you click on the button on Sheet1 it will prompt you for your
password (I set the default to "anything"), then unhide and unprotect Sheet2.
When you click on the button again from Sheet2 it will automatically protect
Sheet2, hide it and switch the focus back to Sheet1.
Good luck!
-Hannah

CODE:
-----------------------------------------------------------------------------------------------
Sub ShowHideSheet2()
'This macro toggles Sheet2 visible/not visible and switches the focus to
'to the appropriate sheet
'
'
'Toggles Sheet2
With Worksheets("Sheet2")
.Visible = Not .Visible

End With
If Worksheets("Sheet2").Visible = True Then
'Unprotects Sheet2.
'Note that the first iteration nothing will happen. Once you click
'the button a couple more times it will prompt you for the password.
'If you don't want your sheet to be protected, simply comment out the
'line below by placing an apostrophe on the left.
Worksheets("Sheet2").Unprotect
'If Sheet2 is visible, switches the active sheet to Sheet2
Worksheets("Sheet2").Activate
Else
'Protects Sheet 2.
'If you don't want your sheet to be protected, comment out the line
'below by placing an apostrophe on the left.
Worksheets("Sheet2").Protect Password:="anything"
'If Sheet2 is hidden, switches the active sheet to Sheet1
Worksheets("Sheet1").Activate
End If
End Sub