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.