Macro to password protect worksheets

[sealion1]

Hi all,

Please find attached the file I am working on - any help would be appreciated

I would like to password protect three tabs in my workbook for "User1", "User2" and "User3". This is so users cannot delete any of the data once it has been submitted into the tab.

However, I would still like the users to be able to view the tabs, but not delete or copy and paste the data.

I have found the following code; but it does not allow the user to add any more data to the tab. Any ideas?

Sub Protect_Example2()

' Loop through all sheets in the workbook
For i = 1 To Sheets.Count

' Activate each sheet in turn.
Sheets(i).Activate

response = MsgBox("Do you want to protect this sheet?", vbYesNo)
If response = vbYes Then
ActiveSheet.Protect password:="larry", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

ElseIf response = vbNo Then
MsgBox ("Sheet not protected")

End If
Next i
End Sub

 

[Hui]

Contents:=True blocks the contents of the worksheet
So you need to set the blank cells to Unprotected before applying the code

 

[sealion1]

Hi Hui,

Is there another way of password protecting the tabs, so that it happens automatically. E.G: Asks once when the spreadsheet opens?

When I try the above approach, everytime I try and do something in the spreadsheet it pops up?

Thanks.

 

[Nebu]

Hi:
I have modified the macros, the macro will basically check for whether the sheets are protected or not, if it is protected it won't give you the message box Please find the attached and let me know with questions if any....