View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben Gord Dibben is offline
external usenet poster
 
Posts: 22,906
Default Different password for different worksheet

Keeping in mind that internal security is very weak in Excel.

Requires VBA code and some passwords or login names.

Sample code.......................

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible with a large message
stating "By disabling macros you have rendered this workbook unusuable.
Please close and re-open with macros enabled"

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook or sheet protection, just code to make a
user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
Select Case Environ("Username")

'if a login is not used change to
'pword = InputBox("Enter Your Password")
'Select Case pword

Case Is = "Gord": Sheets("Gordsheet").Visible = True
Case Is = "Pete": Sheets("Petesheet").Visible = True
End Select
Sheets("Dummy").Visible = False
Exit Sub
endit:
MsgBox "Incorrect Password"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

To allow you to see all sheets and edit them.

In a general module...............

Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub

Naturally you want all this code invisible to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties
and "Lock project for viewing"

Enter a password.


Gord Dibben MS Excel MVP



On Fri, 2 Apr 2010 23:59:01 -0700, Minu
wrote:

Thank you Don. But how to set individual excel sheet passwords for viewing
and editing. i.e only desginated user to view and edit. Appreciate some
help in this matter.

"Don Guillett" wrote:

Excel sheet passwords are easily bypassed.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Minu" wrote in message
...
Dear expert,

I am using MS Excel 2007. I want to create a workbook with different
worksheets for different users to be able to view and edit the content on
his/her designated worksheet. i.e. For security purpose, I want different
worksheet has a different password to
protect so that the only authorized user can view & edit the content on
his/her
designated worksheet.
For example, sheet "marketing" has a password "1234" and sheet "sales" has
a
password "1235" to view and protec. Is it possible? Please kindly advise.

Thanks in advance.


"ryguy7272" wrote:

This is how I do it...

Place the following code in a Sheet (not a module)
Basically, right-click the sheet and click ‘View Code’
Private Sub CommandButton1_Click()


Dim i_pwd As String

i_pwd = InputBox("Please Enter Password to Unhide Sheet", "Unhide
Sheet...")
If i_pwd = "" Then
Exit Sub
End If

'#1
Select Case (i_pwd)
Case Is = "showmktg"
Worksheets("mktg1").Visible = True
Worksheets("mktg2").Visible = True
Worksheets("mktg2").Visible = True
Sheets("mktg1").Select

'#2

Case Is = "showsales"
Worksheets("sales1").Visible = True
Worksheets("sales2").Visible = True
Worksheets("sales3").Visible = True
Sheets("sales1").Select



'#3
Case Is = "showall"
Call ShowSheets


Case Else
MsgBox "Incorrect password; no action taken.", vbInformation, _
"Unhide Sheet..."
End Select

Exit Sub


End Sub


Sub ShowSheets()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If (sh.Name) < "Password" Then
If sh.Visible = xlSheetVeryHidden Then
sh.Visible = True
End If
End If
Next sh

End Sub


Place following code in sheet called ‘ThisWorkbook’
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideSheets
End Sub


Regards,
Ryan---

--
RyGuy


"Freshman" wrote:

Thanks Stefi.

"Stefi" wrote:

Set ToolsProtectionSheet protection individually for each sheets
with
different passwords!

Regards,
Stefi

„Freshman” ezt írta:

Dear expert,

I want to create a workbook with different worksheets for different
users.
For security purpose, I want different worksheet has a different
password to
protect so that the only authorized user can edit the content on
his/her
designated worksheet.
For example, sheet "marketing" has a password "1234" and sheet
"sales" has a
password "1235" to protect. Is it possible? Please kindly advise.

Thanks in advance.


.