Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Protect cell (97/2000/XP)

    I have a cell (F6) on a worksheet that is a vlookup. Next to it is a spinner that scrolls through the lookup and finds a value. It is important that I protect this cell as is being used a reference for another application as a data checker. Basically, I don't want end users to click into the cell and make changes as this will overwrite the function.

    What I would like to do is if they tab into it or click into it to have a message box pop up and give them a warning, when they click OK the focus jumps to another cell.

    Without protecting the sheet Is there a piece of code for this?
    Jerry

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protect cell (97/2000/XP)

    As usual, your advice works like a dream.

    I am a bit new to Excel VBA, so you may find me appearing on the Excel board a bit more than usual, so bare with me.

    Jerry
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protect cell (97/2000/XP)

    >> so bare with me

    As long as it stays within the limits of decency, otherwise I will have to intervene according to <!rule=18>Rule 18<!/rule> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protect cell (97/2000/XP)

    To tell you the truth, I had to think that spelling through. Sorry , "So (teddy) bear with me" <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protect cell (97/2000/XP)

    I'll try too keep out of reach of your chainsaw (at least, that's what I assume it is)

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect cell (97/2000/XP)

    Doesn't seem to work for me, I guess I do something wrong.

    Edited: it only works for me if I *check* the error alert option... XL2000.

    BTW: if it does work, it doesn't <g> when one pastes or drags data on top of the area with this cell...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protect cell (97/2000/XP)

    1) what doesn't work?

    2) I didn't think about copying and pasting over the datacell:
    You could use this instead (assuming the cell is A1, change as appropriate). This will NOT move the cursor, though you could add that to the code
    This goes in the worksheet object not in module.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    End If
    End Sub

    It does use code, but no sheet protection.

    Another option is to protect the sheet and have the linked cell for the spinner UNPROTECTED. You could put the linked cell on another sheet and even HIDE that sheet so the users do NOT have access to this unprotected cell.

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect cell (97/2000/XP)

    I would use the Selection Change event in this case:

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.Range("C1")) Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Range("A1").Select
    Application.EnableEvents = True
    MsgBox "Selecting C1 is not allowed."
    End If
    End Sub
    </pre>

    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect cell (97/2000/XP)

    Hi Steve,

    <hr>1) what doesn't work?<hr>
    If I uncheck the error alert, I can enter anything I like in the cell.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protect cell (97/2000/XP)

    Jan,
    I checked mine again, and you are correct (was there really any doubt? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

    I am not sure how I got it to work originally, (I guess I just didn't test it enough). I might have just looked for the error after hitting <F2>-<enter> and got none and it didn't change (duh).

    I edited my post to remove the item about the error message. Data Validation ONLY works with the ERROR alert ON.

    Steve

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protect cell (97/2000/XP)

    <P ID="edit" class=small>(Edited by sdckapr on 05-Jun-03 08:12. Deleted section on UNCHECKING error alert since this "disables" the data validation.)</P>An easy way to do this (WITHOUT any CODE) is to put the desired formula in the cell, and then validate the cell for input.

    DATA - Validation
    In settings tab:
    Allow: Custom
    Formula: FALSE
    (this will allow NO changes)

    In Input Message Tab Keep "show input message when cell is selected" checked
    add something like this to input message:
    "You can NOT change this Cell"

    Steve

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect cell (97/2000/XP)

    Yet another mystery solved....
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protect cell (97/2000/XP)

    Legare

    I like this piece of code and it works nicely with the application I am designing.

    Steve mentioned in an earlier message in this string about validation rules, which have a role to play but I feel that it is not quite suitable for this application ( no offence Steve <img src=/S/please.gif border=0 alt=please width=31 height=23> ).

    I can see tha I could protect a range of cells using this code, but what if I want it for one more. I tried writing

    ...........................If Not Intersect(Target, ActiveSheet.Range("C1")) or Intersect(Target, ActiveSheet.Range("I4")) ..... but it went haywire

    To cut the story short how can I protect I4 and F6 using this code?
    Jerry

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protect cell (97/2000/XP)

    You could use

    If Not Intersect(Target, ActiveSheet.Range("I4,F6")) Is Nothing Then

  15. #15
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protect cell (97/2000/XP)

    ...and the I assume I could do it for other multiple cells e.g. "a1,a3,f5" etc
    Jerry

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •