Can I password protect a worksheet?!

  Radix Lecti 09:17 07 Mar 2007

And I don't just mean protect it from being edited etc?

I have a tab which I'd like to be un-viewable by those who don't know the password.

I would suspect this requires a snippet of VB, but I'm not sure where to start.

Any ideas clever people?

  rawprawn 09:31 07 Mar 2007

click here Download 7Zip, use it to zip the folder/file and use the password option

  I am Spartacus 09:46 07 Mar 2007

Click Save As and then click on the Tools button in the Save dialogue. Enter the passwords and choose your options.

  Radix Lecti 09:55 07 Mar 2007

Thanks all. However, it's not the whole workbook I need protecting, just one worksheet.

I want everyone to have the ability to view and populate fields on one tab, but only managers (with a password I provide) to be able to populate and view the other tab.

  Eric10 10:06 07 Mar 2007

This method is simple but not foolproof. To hide one or more sheets within a workbook. Select the sheet or sheets to hide then on the Format menu, point to Sheet, and then click Hide. If your sheets have names rather than just Sheet1, Sheet2, etc then it won't be obvious that a sheet is hidden.
To view the sheet then on the Format menu, point to Sheet, and then click Unhide then in the Unhide sheet box, double-click the name of the hidden sheet you want to display.

Excel Help goes on to say "If sheets are hidden by a Microsoft Visual Basic for Applications program that assigns the property xlSheetVeryHidden, you cannot use the Unhide command to display the sheets." but that is beyond my capabilities so I'll pass that over to the spreadsheet guys.

  VoG II 10:22 07 Mar 2007

ALT+F11 to open the Visual Basic Editor. Insert Module. Copy and paste in

Sub hide()
Sheets("Sheet1").Visible = xlVeryHidden
End Sub

(change Sheet1 to the name of the sheet).

Exit the VBE, Tools > Macro > Macros, click hide then the Run button.

Then go back into the VBE, File > Remove Module1.

If you want to get it back again you need similar code but set the .Visible property to True.

  Radix Lecti 10:33 07 Mar 2007

Thanks VoG, does this incorporate a password prompt?

  VoG II 10:43 07 Mar 2007

No there is no password with that. There is with this although anybody who can access the code can see the password.

Sub hide2()
Dim pw As String
Sheets("Sheet1").Visible = xlVeryHidden
pw = Application.InputBox("Enter password")
If pw <> "MyPassword" Then
MsgBox "Wrong password"
Exit Sub
End If
Sheets("Sheet1").Visible = True
End Sub

To hide the sheet run the code and press Cancel. To unhide it run the code, enter the password and click OK.

To hide the code, in the VBE Tools > VBA Project Properties, Protection tab, tick Lock Project for Viewing, enter a password and OK your way out.

  VoG II 10:45 07 Mar 2007

P.S. If you want something more sophisticated click here

  Radix Lecti 10:58 07 Mar 2007

Thanks VoG, I think this should cover it nicely.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?