Excel Error Messages "1005" & VBA

[hotpantsal]

I was hoping that someone could help me out with a problem I am having with Excel Error Messages. What is happening is when a user clicks on a cell in a Worksheet that is protected they receive this error message:

"The Cell or Chart you are trying to change is protected and therefore read-only. To Modify a protected cell or chart, first remove protection using the unprotect sheet command (Tools, Menu, Protection Submenu). You may be prompted for a password."

After you click OK you have to go and unprotect the worksheet or cell. A pop up box then appears asking for a password. Is it possible to have the password box automatically appear without having to click (Tools, Menu, Protection Submenu). That is after first clicking OK on the original message. Is this possible. If so, how would I go about writing this in VBA?

Thanks.

 

[michaelbr55]

Why does the sheet or cell need protection in the first place if the user is able to disable it anyway?

 

[skinicod]

You could create your own style of protection by placing something within an excel sheet object:

Public Password As String

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address = "$A$1" Then Exit Sub
If Password = "password" Then Exit Sub
Password = InputBox(prompt:="Please Enter Password&quot
If Password = "password" Then Exit Sub
Range("A1&quot.Select
End Sub

In this scenario A1 is the only cell that the user may enter, but the code could be altered to apply to which ever cells you want to protect...