Excel Worksheet Protection 2

[Mike555]

We all know that Excel worksheets can be password protected to prevent unauthorized changes, but can individual worksheets also be protected so that they cannot be viewed without first entering a password? I need each worksheet in a workbook to have its own password, and a user cannot even view that sheet until the password is entered. Any ideas?

--
Mike

Man usually avoids attributing cleverness to somebody else -- unless it is an enemy. - A. Einstein

 

[SeekingAnswers2003]

Mike555,

You could try to have an Excel File that will work like an index to the different sheets. You then have the sheets in different Excel files password protected. On the index you can have the title as a hyperlink to the password protected files.

This way you can give specific passwords needed to access that specific Spreadsheets.

Daniel.

 

[KenWright]

You would need to have a file that already has all but one sheet hidden, which could be an index or summary sheet (You must have one that is not). Then have a button/s or something on the index sheet which when pressed will initiate a macro that will prompt the user for a password, and then on receipt of the correct password will unhide the relevant sheet. You don't say if more than one person will be using the file, but you can either have a number of buttons pointing to different sheets, or just have one button and then depending on the password entered have it show the relevant sheet.

You can even set the sheets via code initially to be xlVeryHidden which means they will not even be able to see that there are sheets there to be unhidden. You will need to protect the VBA code as well though, as you don't want them switching into the VBE finding the paswwords and then running the routine.

Regards
Ken...............

 

[Mike555]

KenWright...Could you me with provide sample code that prompts a user for a password? I'm not experienced with VBA for Excel, but if you provide a sample I'll figure it out..

SeekingAnswers...Can cells from multiple spreadsheets in multiple locations be formulated in one spreadsheet? IE...adding cell A1 from file A and cell B4 from file B?

Thanks!

--
Mike

Man usually avoids attributing cleverness to somebody else -- unless it is an enemy. - A. Einstein

 

[SeekingAnswers2003]

Mike555,

Yes. on the cell you want the formula just do the following:
Put the equal sign (=). This will let Excel know that this cell will contain a formula.
Then you can go to the first cell on the different sheet in your file and click on it with your mouse. (You will see that on your formula cell it will make reference to the sheet and cell).
So after clicking on that cell you can put the mathematical operander (ie +, -, *. /, etc) and then you can go to the other cell and click on it.

You can add as many cells as you wish, when done just hit enter.

You can also combine different cells from different files. You do it exactly the same way.

Daniel.

 

[KenWright]

This assumes you have a workbook with 5 sheets:-

1 - 'Index'
2 - 'Sht 1'
3 - 'Sht 2'
4 - 'Sht 3'
5 - 'Sht 4'

Whilst in the VBE (ALT+F11), put the following code into the 'ThisWorkbook' module:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
For i = 2 To Worksheets.Count
If Sheets(i).Visible = True Then
Sheets(i).Visible = xlVeryHidden
End If
Next i
Application.DisplayAlerts = False
ActiveWorkbook.Save
End Sub

This will automatically hide all sheets except the index sheet when the file is closed.

Now in a normal module put the following:-

Private Sub Pword()

Dim ans As String

ans = InputBox("Password Please&quot

Select Case ans
Case Is = "pword1"
Sheets(2).Visible = True
Case Is = "pword2"
Sheets(3).Visible = True
Case Is = "pword4"
Sheets(4).Visible = True
Case Is = "pword4"
Sheets(4).Visible = True
Case Else
MsgBox ("You are not authorised to view this file - Goodbye&quot
Application.DisplayAlerts = False
Application.Quit
End Select
End Sub

Then put a button from the forms toolbar onto the index sheet and assign a macro with the following name:-

'Pword test.xls'!Pword

Finally you need to protect the VBA code so no-one can see it. Hit ALT+F11 - take a look at the top left of your screen, and hopefully you will see a window entitled VBA Project Explorer. In this window there will be a list of filenames, just like the folder names in an Explorer window. Find the name of your file in that window and right click on your workbook name and then from the menu that pops up, select VBAProject properties. This will then give you a grey dialog box with two tabs. Click on the Protection Tab, put a tick in the 'Lock Project for viewing' option, put in a password and confirm it below, then hit OK. Do File / Save Workbookname, File Close and return to Excel, then close file and reopen.
You should now not be able to get to the VBAProject Code if you go into the VBE.

Regards
Ken.................

 

[Mike555]

KenWright, I used your code and it worked like a charm..Thanks.

One additional question...What exactly does the 'Pword test.xls'!Pword macro name do? I've never seen a macro execute a function simply using its name.

Thanks!

--
Mike

Man usually avoids attributing cleverness to somebody else -- unless it is an enemy. - A. Einstein