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.