the protected sheet error message

Either nobody had any good ideas for that problem or I didn'texplain it well. So, at this point, I'm wondering if that particularerror message can be trapped or 'shut off" . . . the Protected sheetmessage, when someone tries to change something on the page . . . anyideas for a work-around?

Share:

8 Answers Found

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

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

Your question brings up assumptions not presented in Scott's originalpost (see below). What you want to accomplish requires thinking outsidethe box. You can use the WorksheetSelectionChange for your issue. Allyou need do is identify the cells allowed to be changed within yourmodule. If the users selection does not meet this criteria, then amessage box can be presented to the user instructing other wise. You caneven take this one step further by highlighting all the allowable cellsfor the user. If the selected range meets the program requirements, thensimply unprotect the worksheet as explained, and lock the spreadsheetupon completion.

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

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 thecell 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 lockedcell "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 aprotected 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 throughwhat everyone said, I think I found the solution. At least itseems to work, and I tested it about 5 times, trying to check asmany 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 directlyIf Sh.Name = "Info" ThenMsgBox "The Info sheet is linked to the individual teamsheets, so it cannot be changed directly. " & vbCrLf & "Please makechanges on the individual Team sheets."Exit Sub

ElseIf Sh.Name = "CallData" ThenMsgBox "The CallData sheet is linked to the individual teamsheets, so it cannot be changed directly. " & vbCrLf & "Please makechanges on the individual Team sheets."Exit Sub

ElseIf Sh.Name = "Namelist" ThenMsgBox "The Namelist sheet is linked to the individual teamsheets, so it cannot be changed directly. " & vbCrLf & "Please makechanges on the individual Team sheets."Exit Sub

ElseIf Sh.Name = "Total Stats" ThenMsgBox "The Total Stats sheet is linked to the individualteam sheets, so it cannot be changed directly. " & vbCrLf & "Pleasemake changes on the individual Team sheets or make changes afterFinal reports have been prepared."Exit Sub

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

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

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

In answer to the questions about what I want to happen, I don'twant them to actually change any of the sheets directly, but insteaddo all of their changes through the userform (QA) which minimizesthe potential data noise. However, I do want the userform itself tobe able to make changes, so this way if they change the selection ona Team sheet, they either have to do so in Column 1 or the selectionis 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 themessage I put in that "<this sheet> cannot be changed directly, goto the Team sheets" . . . so if they are very confused and still tryto type in that cell, then the Protected sheet error appears, sothat works fine. By then, they 'deserve it' so to speak.

With that in mind, I really don't need to actually trap thatparticular error message any more, I don't think. I would beinterested in other ways to do stuff like this, though, if anyonehas suggestions.

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

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