I understood you and was also hoping for an answer. I was able to
eliminate (not trap) the "cold" pop-up in my application by setting the
Cancel boolean as below. Obviously this only works on Double click but
it's a help and you probably want to use Target to control the
canceling.



Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
Cancel = True

Assuming your sheet  is protected  with a password, and you know the
password, you only need add the following code to your procedure.

ActiveSheet.Unprotect "Password"
Do Stuff
ActiveSheet.Protect "Password"

How would one keep the Cell (formula or value) from being inadvertently
changed since Scott wants those cells to be locked and ideally a pop-up
instructing the user to choose the appropriate Cell(s).

The WorksheetSelectionChange would fire but without a "CANCEL" we are in
the same predicament True?

The original question was very basic, and asked how to bypass the error
message. The answer given accomplishes that goal. It is up to the
programmer to write code in a way that achieves the final outcome.

Your question brings up assumptions not presented in Scott's original
post (see below). What you want to accomplish requires thinking outside
the box. You can use the WorksheetSelectionChange for your issue. All
you need do is identify the cells allowed to be changed within your
module. If the users selection does not meet this criteria, then a
message box can be presented to the user instructing other wise. You can
even take this one step further by highlighting all the allowable cells
for the user. If the selected range meets the program requirements, then
simply unprotect the worksheet as explained, and lock the spreadsheet
upon completion.

Locking the spreadsheet can be done at any time, such as when the
workbook is closed. You can do this because your 'Change' event is
making sure that only the required cells are being touched. I.E. if a
range that is not to be touched is selected, you can always make sure to
set an activecell yourself within the code, thus forcing the users
selection to another range.

Your mostly right but the "Original" Original message  (the one
Scott is referring to) was wrestling with avoiding the pop-up altogether
and either creating a new pop-up or redirecting to valid Cells.

Sorry, didn't notice the original, but at least you have what you need
to move forward.

It's really going to depend on what you WANT to happen.
for instance:
You have some cells that can be modified, but others that you want to
"lock".
Do you want the "locked" cells to be selectable?
If not, you CAN use the SelectionChange event to check to see if the
cell selected is A)locked, and B)protection is on. If both are true,
then the "focus" can be changed to another cell, thereby making the locked
cell "un-selectable".
This is similar to the use of a "form" in MS-Word.

Once again, what is it you WANT to have happen.
the protected  Cell message  appears AFTER the user tries to modify a
protected cell.
Do you want to PREVENT them from performing the illegal operation,
or ignore them after they've tried?

I'll continue looking for a way to capture the message, but until then,
I hope this helps.

After some sifting through
what everyone said, I think I found the solution. At least it
seems to work, and I tested it about 5 times, trying to check as
many relevant situations as I could think of.

Here's the code that I came up with:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Call protsheets
'<---"protsheets" turns protection on for the sheets
'<---which aren't to be changed directly
If Sh.Name = "Info" Then
MsgBox "The Info sheet  is linked to the individual team
sheets, so it cannot be changed directly. " & vbCrLf & "Please make
changes on the individual Team sheets."
Exit Sub

ElseIf Sh.Name = "CallData" Then
MsgBox "The CallData sheet is linked to the individual team
sheets, so it cannot be changed directly. " & vbCrLf & "Please make
changes on the individual Team sheets."
Exit Sub

ElseIf Sh.Name = "Namelist" Then
MsgBox "The Namelist sheet is linked to the individual team
sheets, so it cannot be changed directly. " & vbCrLf & "Please make
changes on the individual Team sheets."
Exit Sub

ElseIf Sh.Name = "Total Stats" Then
MsgBox "The Total Stats sheet is linked to the individual
team sheets, so it cannot be changed directly. " & vbCrLf & "Please
make changes on the individual Team sheets or make changes after
Final reports have been prepared."
Exit Sub

ElseIf Target.Row = 1 Then
'<---row one is only column labels
Exit Sub

ElseIf Target.Column = 1 Then
Call setfilnames
'<---records activecall, activesheet on "Info" sheet
'<---so after the userform, that sheet and cell is selected
Call QA
'<---calls the userform for filtered editing of data

ElseIf Target.Column <> 1 Then
newcol = 1
targetr = Target.Row
Cells(targetr, newcol).Select
'<---selects column 1 of the selected row
'<---which fires this macro again, so the userform appears
End If
End Sub

Sub protsheets()
For Each Sh In Sheets
Sh.Protect UserInterfaceOnly:=True
Next Sh
End Sub

Of course, if any of you have a better way to do this same
thing, I'd love to hear it.

In answer to the questions about what I want to happen, I don't
want them to actually change  any of the sheets directly, but instead
do all of their changes through the userform (QA) which minimizes
the potential data noise. However, I do want the userform itself to
be able to make changes, so this way if they change the selection on
a Team sheet, they either have to do so in Column 1 or the selection
is autochanged to the Column 1 cell in that row, and in either case,
the userform fires. Mission accomplished.

Like I said, with all the tests I run so far, this works great.
And the only time the protected  sheet error  appears is AFTER the
message I put in that "<this sheet> cannot be changed directly, go
to the Team sheets" . . . so if they are very confused and still try
to type in that cell, then the Protected sheet error appears, so
that works fine. By then, they 'deserve it' so to speak.

With that in mind, I really don't need to actually trap that
particular error message  any more, I don't think. I would be
interested in other ways to do stuff like this, though, if anyone
has suggestions.

The only thing I haven't been able to figure out is a very very
minor thing. That is . . . on a supervisor's Team sheet if a cell
in Column 1 is ALREADY SELECTED and the user clicks on it, nothing
happens, because the selection hasn't CHANGED--and the code is
firing from the SelectionChange event. So, they have to select
another cell. But since the sheets are protected from
userinterface, they still can't change the cell directly. I
couldn't see a way around that part, but it does happen when a sup
opens up a page  . . . and selects the cell they had last selected.
The non-computer types might get a bit frustrated with that, but
that might just be part of it, anyway.

At this point, I'm very happy with what I have, so if anyone has
any ideas  about how to fix that last little minor thing, that would
be fantastic.