custom error message in popup window for protected cell?

[jayd4wg]

Here's one to stump you guys...

I have a worksheet for my employees and I only want them to be able to alter/enter data in certain cells...that much I have covered.

NOW what I want to do is replace the standard "the cell or worksheet your are trying to change is read-only and therefor cannot be changed blah blah blah" to say something like "HEY DORK...you can only fill in certain fields, quit trying to re-write history!" so they will quit trying to redesign my spreadsheets.. Any thoughts?

 

[Norie]

Here's one to stump you guys...

I have a worksheet for my employees and I only want them to be able to alter/enter data in certain cells...that much I have covered.

NOW what I want to do is replace the standard "the cell or worksheet your are trying to change is read-only and therefor cannot be changed blah blah blah" to say something like "HEY DORK...you can only fill in certain fields, quit trying to re-write history!" so they will quit trying to redesign my spreadsheets.. Any thoughts?

What do you actualy want?

I can think of at least 3 ways of doing what you want, but since you think we can't do it why bother answering your query.

 

[Zack Barresse]

.. but since you think we can't do it why bother answering your query.

Excuse me? You have no right to post this Norie. Please stop.

As far as answering your question jayd4wg, you'd need VBA to do that. First of all, let me explain that Excel is not a secure environment, and should not be boasted as such. It is also my opinion that putting something like, "HEY DORK, stop changing blah, blah, blah...," will only inspire people to try and break your protection (which is very, very easy, like < 1 min.); plus it just seems mean.

But, to answer your question, you can right click the sheet tab in question and paste something like this on the right ...

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim goodRng As Range
    If Target.Locked Then
        Application.EnableEvents = False
        Range("A10").Activate 'set to desired unlocked cell
        Application.EnableEvents = True
        MsgBox "Stop!" & vbNewLine & vbNewLine & _
            "The cell(s) you are trying to alter are protected" & vbNewLine & _
            "and should not be altered without prior" & vbNewLine & _
            "authorization." & vbNewLine & vbNewLine & _
            "Thank you," & vbNewLine & _
            "Management", vbCritical, "STOP!"
    End If
End Sub

J-walk has some user tips in Strong Arm Tactics .. here: http://j-walk.com/ss/excel/tips/tip100.htm

 

[lenze]

Firefytr's code is quite good, but here is another approach

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("myRange")) Is Nothing Then Exit Sub
MsgBox ("your nasty message goe here")
Range("$A$1").Select
End Sub

myRange is the name for all the cells you don't want to be edited. This approach will trigger the MsgBox whenever a cell in myRange is selected. It will also reset the focus to A1, never giving the user a chance to enter data.

HTH

lenze

 

[Zack Barresse]

jayd4wg: Btw, this is easily bypassed if the user only elects to Disable Macros (hence the link to J-Walk's page on brute tactics). This method (and Lenze's method as well) will be triggered if a multi-cell range is selected and the specified targets are within this selected range, fyi.

Lenze: I love your sig. How true!

 

[Norie]

firefytr

I'm sorry if I've offended you in some way and will now stop posting to this forum and any others where I, stupidly obviously, thought I might have been giving anybody help.

Bye

 

[jayd4wg]

politics and brown nosing aside, the "hey dork" comment was meant as humor... perhaps too much scotch blurred the invisible wink smiley I should have put there

(sure hope THIS image isn't taken the wrong way...)

I knew from the beginning that what I was trying to do would come from a tasty tidbit of VBA and the problem is...at least from my standpoint...is that I don't have enough VBA under my belt to be as proficient as I want to be. I have a year of VB.net and some java and other languages...hell I started in BASIC but no matter.

Thanks for the code, I'm implementing it as soon as this novel is over. Norie...I didn't take offense to your message rather I thought perhaps I didn't explain my wants and desires enough.

I lack a full knowledge of the possible events available in VBA, and some of the syntax and I'm hoping that soon I'll acquire a decent reference...but for the time being this site has been my reference and will continue to be. Thanks for all the help AGAIN and I really hope noone stops posting b/c of this...

 

[Lokai]

Hi, the code above stops users from selecting protected cells. It does not let them cursor thru the page.

The original question (and my question also) is how to change the error message when a user trys to "change" a protected cell.

I've tried trapping the error number (i think its 70) and popping up a msgbox on the error but to no avail.

 

[lenze]

By using the TAB key, the user can navigate to all unprotected cells. The codes above prevent them from selecting a protected cell. but why would they need to??

lenze

 

[jayd4wg]

I will say this code has worked PERFECTLY for me. I am basically using a Form for a timesheet and do not want certain pre-filled cells altered in any way. When the user selects any of these, I fire the popup just letting the user know that those cells ARE protected...but also tell them they can remove the protection as well.

Why would you want someone to even select a protected cell if they were not going to change it? there are scroll bars and arrow buttons for navigation...as well as the before-mentioned tab key to navigate with.

Maybe post a screenshot of the sheet you are working on to accompany your explanation?