MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

possible to change Protected Sheet error message?

  Asked By: Alex    Date: Dec 18    Category: MS Office    Views: 1728

I was unable to find
anything on after I looked. I might just not know the right
terminology to search for it, but I couldn't find anything up to now.

In the program for the call handling which you've all helped me with
before, I planning to use:

ActiveSheet.Protect UserInterfaceOnly:=True

to protect the cells from direct user interface, and that works fine.

However, the situation is that I set up the SelectionChange
event to fire only when the user clicked in the first column of the
sheet, because it make the offsets so easy to maintain
systematically--that is, I always knew that the point of beginning
was going to be Column A for whatever changes the input from the
userforms made. That way, it was only a matter of counting the
columns to locate the exact position of the data being changed.

So, I was wondering if I could change the error message that
pops up when an attempt is made to directly change a cell's contents
on a protected sheet, so as to remind all the users to always select
the call to be examined by clicking ONLY on the first column of the
row they want to evaluate (which then pops up the various user forms
for them to do that).

That allows me to filter all the input to reduce noise in the
data, and I've got that part working well.

So, does anyone know where I might look to find out how to
change an error message like that? . . . and if it's not possible to
change that error message, is there another way to remind all the
users to click only on the first column when they accidently attempt
to change the sheet directly?

Thanks for your help, and I'm still working on a good way to do
the logins that the group helped me with before--so, as soon as I
complete that, I'll let you all know how it turned out, since you
all were so helpful about it.



7 Answers Found

Answer #1    Answered By: Rae Fischer     Answered On: Dec 18

Not answering your actual question, but ...

> However, the situation is that I set  up the SelectionChange
> event to fire  only when the user  clicked in the first column  of the
> sheet, because it make the offsets so easy  to maintain
> systematically ...

You can take control of your columns in a different way. I'm assuming that
the cell that is clicked  is called Target in your event sub. Then

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim TargetRow As Integer: TargetRow = Target.Row
Cells(TargetRow, "F").Value = "xx"
End Sub

I.e. grab the row number from the target cell and use the column you want in
cells  call. My example puts an "xx" in column F of any row you click  in.

> That way, it was only a matter of counting  the
> columns to locate  the exact position  of the data  being changed.

For my way, there's no counting at all - just use the column's letter (or

If you want a group  of adjacent cells, then something like

Range(Cells(TargetRow, "J"), Cells(TargetRow, "L")).Value = "zz"


Range("J" & TargetRow & ":L" & TargetRow).Value = "zz"

can be useful - and still no counting.

Answer #2    Answered By: Harriet Ward     Answered On: Dec 18

I'm not sure you saw the ongoing discussion from my later thread
on the same situation starting with message  #7416 . . . with the
working solution --at that point--that is in Message #7430.

The method you just listed DOES sound like a very simple way to
address the cell locations, thank you. I'm going to re-evaluate my
code to see if it would refine the process.

I know everyone here in the group  is busy and volunteering their
time, so perhaps people missed the part  that I was unable  to answer
in my code. I'm very grateful for all the help  I get here, anyway.
It's a very very minor thing, but I am curious if it can be
handled. Unless I misunderstood you, this particular part of the
question wasn't answered by your suggestion, either. That part is:

--copied from previous message #7430:
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.<----end of copied paragraph

I'm interested in that part. Any ideas?

Answer #3    Answered By: Darwishi Massri     Answered On: Dec 18

I did scan the discussion - just thought I'd toss in a different idea.

But I did miss the "already selected" part. This can be an annoyance with
selection events. A benefit of my suggestion is that you can click  on a
different cell in the same row - you're not limited to Column A.

As far as I know there is no way to get a selection change  event, or any
other event, by clicking  on the already-selected cell. However, you do get
a "before double click" event if you double click a cell. Perhaps that's an

The converse problem is that you sometimes get too many selection change
events. Every time you move the focus, you're going to get one, and you
need to handle it.

In general, I like to avoid selection change events, because they can be a
pain. But sometimes, they're either necessary, or simply the best approach.

Answer #4    Answered By: Binge Fischer     Answered On: Dec 18

More 'Already Selected Fix' Ideas. When the user  wants to select a
cell that was selected when the sheet  was saved. In the BeforeSave
event handler, change  the cell selection - forcing the user to
re-select and therefore fire  the Selection_Change.
If this is awkward because the user may not remember where he was.

Go ahead and change the selection BeforeSave, as above, but
remember/save the cell(location/address) the user had selected in a
cell on a hidden sheet. When the workbook opens, pop up a UserForm
that asks the user if he wants to resume where he left-off. If the
answer is Yes, let VBA re-select his cell (leave events enabled) and
let the Selection_Change fire. Your data  entry UserForm will pop up
as usual.

Answer #5    Answered By: Allison Stanley     Answered On: Dec 18

Yes indeed, you have a helpful suggestion, thanks. In fact, it
occurred to Me that a similar set  of steps might be incorporated into
other sequences in the program. After all, the situation of "already
selected" happens most often AFTER the user  closes a user form or puts
things on "Pause" because of a phone call. It occurred to me that I
could include more than one level of "interruption" choices.

They already have the "Panic Button" which saves everything and
closes the program  down entirely for a significant interruption. But
there are others which are not so drastic. Like, a supervisor might
get a personal call  from hubby or wife or kid just to ask something
that would not require more than a few moments, but they still might
need to reduce the screen from view since the info is security level.

So, the "Pause" button could cover everything from view and then
re-up the userform at the same place as it was before the "Pause" in a
similar way as you said.

And, I like the whole idea of what you described for a "Panic
Button" save situation, anyway . . . because then the user CAN have
the choice to get back to where they were, and would not have to click
on another cell to get the userform to fire.

Thanks, and as I said to David, I'll post the code when I get it

Answer #6    Answered By: Xander Thompson     Answered On: Dec 18

Thanks again for your ideas, all of them. For me at least, it
always helps to see things from another angle, even if I don't end up
using that particular code at the time.

The code I had used and posted in that earlier thread also includes
allowing the user  to click  on any column  with the same result. I do
think that the code you offered is a bit tighter and easier to use for
the column locations, though, so I'm still a couple of steps
better-off with your suggestions.

I'm thinking that it might be good  to try the BeforeDoubleClick
event, but as you said having too many events firing could be a
problem. After I test it, and if I get some workable code going, I'll
post it here.

I WAS curious however--if you avoid the SelectionChange event,
what other ways would you use? I understood your point and the logic
of it, just didn't know what other alternatives might be there that I
haven't seen thus far.

Again, thanks for your suggestions, and I hope this message  gets
thru since there's a winter storm going great guns here at the moment.

Answer #7    Answered By: Nathan Evans     Answered On: Dec 18

I probably worded that badly.

I try to avoid the type of system design where a selection change  would be
necessary to achieve the result. It tend to favour either more active
solutions (button clicks or cell change events) or more passive solutions
(values deduced from other cells, rather than cells  filled with specific
values at the time).

On occasions, though, a selection change is certainly the best way to go.

Didn't find what you were looking for? Find more on possible to change Protected Sheet error message? Or get search suggestion and latest updates.