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.

Hi Freshman,

In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have,
however, is that your users will forget to re-protect the worksheet before saving & closing the workbook.

For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password
for it.

--
Cheers
macropod
[MVP - Microsoft Word]


"Freshman" wrote in message ...
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.

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.

Hi macropod,

I'm interested to have a macro to protect worksheets with different
passwords, then I can assign the passwords to each individual user. May I
have the code as I'm a VBA idiot.

Thanks.

"macropod" wrote:

Hi Freshman,

In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have,
however, is that your users will forget to re-protect the worksheet before saving & closing the workbook.

For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password
for it.

--
Cheers
macropod
[MVP - Microsoft Word]


"Freshman" wrote in message ...
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.

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.

Hi Freshman,

Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module.

Option Explicit
Dim User As String
Dim UPwd As String
Dim WPwd As String
Dim Err As Boolean
Dim wsSheet As Worksheet
Dim wsActvSht As Worksheet

Private Sub Workbook_Open()
WPwd = "" 'Inset the Workbook Password between the double quotes
Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
wsActvSht.Activate
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = wsActvSht.Name Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetVeryHidden
End If
End With
Next wsSheet
Restart:
User = InputBox("Please Input your Workbook Username")
UPwd = InputBox("Please Input your Workbook Password")
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then
On Error GoTo Restart
If .ProtectContents = True Then .Unprotect UPwd
.Visible = xlSheetVisible
.Activate
Exit Sub
End If
End With
Next wsSheet
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their
worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the
prompt is repeated. If the prompts are left empty, the code does nothing more and exits.

The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code.
This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc.

You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll
leave that to you.

--
Cheers
macropod
[MVP - Microsoft Word]


"Freshman" wrote in message ...
Hi macropod,

I'm interested to have a macro to protect worksheets with different
passwords, then I can assign the passwords to each individual user. May I
have the code as I'm a VBA idiot.

Thanks.

"macropod" wrote:

Hi Freshman,

In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have,
however, is that your users will forget to re-protect the worksheet before saving & closing the workbook.

For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a
password
for it.

--
Cheers
macropod
[MVP - Microsoft Word]


"Freshman" wrote in message ...
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.

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.

hi.
the first code by macropod is awesome..
what changes will i have to be made if i want the multiple worksheet to remain visible untill i close my workbook.

For ex.
i opened one worksheet after entering password
but i want to open another worksheet by entering different password .
now i want them to remain visible till i close the workbook

amutak



macropod wrote:

Hi Freshman,Here's some code to get you started.
28-Jul-08

Hi Freshman,

Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module.

Option Explicit
Dim User As String
Dim UPwd As String
Dim WPwd As String
Dim Err As Boolean
Dim wsSheet As Worksheet
Dim wsActvSht As Worksheet

Private Sub Workbook_Open()
WPwd = "" 'Inset the Workbook Password between the double quotes
Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
wsActvSht.Activate
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = wsActvSht.Name Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetVeryHidden
End If
End With
Next wsSheet
Restart:
User = InputBox("Please Input your Workbook Username")
UPwd = InputBox("Please Input your Workbook Password")
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then
On Error GoTo Restart
If .ProtectContents = True Then .Unprotect UPwd
.Visible = xlSheetVisible
.Activate
Exit Sub
End If
End With
Next wsSheet
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their
worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the
prompt is repeated. If the prompts are left empty, the code does nothing more and exits.

The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code.
This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc.

You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll
leave that to you.

--
Cheers
macropod
[MVP - Microsoft Word]


"Freshman" wrote in message ...

Previous Posts In This Thread:

On Monday, July 28, 2008 2:59 AM
Freshma wrote:

Different password for different worksheet
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.

On Monday, July 28, 2008 3:24 AM
macropod wrote:

Hi Freshman,In short, yes, It's possible.
Hi Freshman,

In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have,
however, is that your users will forget to re-protect the worksheet before saving & closing the workbook.

For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password
for it.

--
Cheers
macropod
[MVP - Microsoft Word]


"Freshman" wrote in message ...

On Monday, July 28, 2008 3:30 AM
Stef wrote:

Set ToolsProtectionSheet protection individually for each sheets with
Set ToolsProtectionSheet protection individually for each sheets with
different passwords!

Regards,
Stefi

???Freshman??? ezt ??rta:

On Monday, July 28, 2008 3:43 AM
Freshma wrote:

Hi macropod,I'm interested to have a macro to protect worksheets with
Hi macropod,

I'm interested to have a macro to protect worksheets with different
passwords, then I can assign the passwords to each individual user. May I
have the code as I'm a VBA idiot.

Thanks.

"macropod" wrote:

On Monday, July 28, 2008 3:44 AM
Freshma wrote:

Different password for different worksheet
Thanks Stefi.

"Stefi" wrote:

On Monday, July 28, 2008 5:46 AM
macropod wrote:

Hi Freshman,Here's some code to get you started.
Hi Freshman,

Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module.

Option Explicit
Dim User As String
Dim UPwd As String
Dim WPwd As String
Dim Err As Boolean
Dim wsSheet As Worksheet
Dim wsActvSht As Worksheet

Private Sub Workbook_Open()
WPwd = "" 'Inset the Workbook Password between the double quotes
Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
wsActvSht.Activate
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = wsActvSht.Name Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetVeryHidden
End If
End With
Next wsSheet
Restart:
User = InputBox("Please Input your Workbook Username")
UPwd = InputBox("Please Input your Workbook Password")
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then
On Error GoTo Restart
If .ProtectContents = True Then .Unprotect UPwd
.Visible = xlSheetVisible
.Activate
Exit Sub
End If
End With
Next wsSheet
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their
worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the
prompt is repeated. If the prompts are left empty, the code does nothing more and exits.

The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code.
This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc.

You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll
leave that to you.

--
Cheers
macropod
[MVP - Microsoft Word]


"Freshman" wrote in message ...

On Tuesday, July 29, 2008 1:24 PM
ryguy727 wrote:

This is how I do it...
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:

EggHeadCafe - Software Developer Portal of Choice
SharePoint Site Provisioning ? The Real World
http://www.eggheadcafe.com/tutorials...provision.aspx

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.

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.

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.

.

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.

.

On Monday, 28 July 2008 12:54:29 UTC+5:30, macropod wrote:
Hi Freshman,

In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have,
however, is that your users will forget to re-protect the worksheet before saving & closing the workbook.

For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password
for it.

--
Cheers
macropod
[MVP - Microsoft Word]


"Freshman" wrote in message ...
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.

I tried this, but is there an option to place a password? I could just see set permissions, wherein only permitted people can use the sheet. But couldnt find any password set up

Hi Macropod,

Could you please help me with the same code. In case I want to create a result sheet but don't want a student to see other student's marks.

So, how can I add different passwords for different students.

Hi Macropod,

Could you please help me with the same code. In case I want to create a
result sheet but don't want a student to see other student's marks.

So, how can I add different passwords for different students.

Are you trusting that the students won't share passwords?

Instead of a 'results sheet', why not just display the results for the student
in a msgbox -OR- a designated field on the form where the StudentName:Password
gets input?

Otherwise, explain how you want this 'results sheet' approach to work!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Gary,

Not able to reply to the email that you sent. So asking here.

Thank you for replying.

I have no other option but to trust the integrity.

So, I used your below code to create a test file and I kind of thought it as best option instead of manually copy pasting results in email or providing hard copies.

However, i am not able to close the file as it gives me error message.

Step 1: Input Username or name of Worksheet - Name of Student.

Step 2- Input Unique password for each worksheet which is each password for each student

Step 3- Result. According to username password it will open respective sheet only.

Step 4- Close the workbook. Here I am facing an error without saving anything.


It Highlights this part of the code.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True



I need your help with this code. I think the code is working well after the error will be rectified just that in the code I am not able to set unique password for unique username(worksheet name). I can only set a single password for all username using this code.

Also, how can I have a blanket access to add marks of students if that is not possible then I will add one raw data worksheet and put in formulaes in students worksheet to vlookup the results from raw data worksheet.. Does that sound good?
Again, thank you for helping me out on the code. I really appreciate this.



On Monday, July 28, 2008 at 3:16:58 PM UTC+5:30, macropod wrote:
Hi Freshman,

Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module.

Option Explicit
Dim User As String
Dim UPwd As String
Dim WPwd As String
Dim Err As Boolean
Dim wsSheet As Worksheet
Dim wsActvSht As Worksheet

Private Sub Workbook_Open()
WPwd = "" 'Inset the Workbook Password between the double quotes
Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
wsActvSht.Activate
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = wsActvSht.Name Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetVeryHidden
End If
End With
Next wsSheet
Restart:
User = InputBox("Please Input your Workbook Username")
UPwd = InputBox("Please Input your Workbook Password")
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then
On Error GoTo Restart
If .ProtectContents = True Then .Unprotect UPwd
.Visible = xlSheetVisible
.Activate
Exit Sub
End If
End With
Next wsSheet
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their
worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the
prompt is repeated. If the prompts are left empty, the code does nothing more and exits.

The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code.
This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc.

You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll
leave that to you.

--
Cheers
macropod
[MVP - Microsoft Word]

Hi Gary,

Not able to reply to the email that you sent. So asking here.

Thank you for replying.

I have no other option but to trust the integrity.

So, I used your below code to create a test file and I kind of thought it as
best option instead of manually copy pasting results in email or providing
hard copies.

However, i am not able to close the file as it gives me error message.

Step 1: Input Username or name of Worksheet - Name of Student.

Step 2- Input Unique password for each worksheet which is each password for
each student

Step 3- Result. According to username password it will open respective sheet
only.

Step 4- Close the workbook. Here I am facing an error without saving
anything.


It Highlights this part of the code.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True
Then ThisWorkbook.Unprotect WPwd For Each wsSheet In
ActiveWorkbook.Worksheets With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True



I need your help with this code. I think the code is working well after the
error will be rectified just that in the code I am not able to set unique
password for unique username(worksheet name). I can only set a single
password for all username using this code.

Also, how can I have a blanket access to add marks of students if that is not
possible then I will add one raw data worksheet and put in formulaes in
students worksheet to vlookup the results from raw data worksheet. Does that
sound good? Again, thank you for helping me out on the code. I really
appreciate this.



On Monday, July 28, 2008 at 3:16:58 PM UTC+5:30, macropod wrote:
Hi Freshman,

Here's some code to get you started. It goes into your workbook's
'ThisWorkbook' vba module.

Option Explicit
Dim User As String
Dim UPwd As String
Dim WPwd As String
Dim Err As Boolean
Dim wsSheet As Worksheet
Dim wsActvSht As Worksheet

Private Sub Workbook_Open()
WPwd = "" 'Inset the Workbook Password between the double quotes
Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain
visible If ThisWorkbook.ProtectStructure = True Or
ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
wsActvSht.Activate For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = wsActvSht.Name Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetVeryHidden
End If
End With
Next wsSheet
Restart:
User = InputBox("Please Input your Workbook Username")
UPwd = InputBox("Please Input your Workbook Password")
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then
On Error GoTo Restart
If .ProtectContents = True Then .Unprotect UPwd
.Visible = xlSheetVisible
.Activate
Exit Sub
End If
End With
Next wsSheet
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows =
True Then ThisWorkbook.Unprotect WPwd For Each wsSheet In
ActiveWorkbook.Worksheets With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

The code works by automatically hiding all except a 'main' worksheet upon
opening and closing. It then asks the user for their worksheet name and
password. If the correct password for the named sheet is given, it is
displayed and unprotected; otherwise the prompt is repeated. If the prompts
are left empty, the code does nothing more and exits.

The code is also written around the principle that the workbook itself
should be protected - see the relevant comments in the code. This is to
protect its structure etc, so that you won't run into problems with sheets
being renamed etc.

You'll probably want to add more code to trap 'Save' events, in case the
user saves the workbook with their worksheet visible. I'll leave that to
you.

--
Cheers
macropod
[MVP - Microsoft Word]



Can you upload the file to an online place I can download it from so I can see
exactly what you are doing?

FWIW:
I have a StudentGradesManager addin that handles this nicely, but in a
different way; - 1 sheet per class, student records are filtered for viewing.
In your case of 1 sheet per student -OR- a separate 'report' sheet per query, a
secure solution should be fairly simple to implement if I know the structure of
your file.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion