Closed Thread
Results 1 to 8 of 8

How do i allow users to edit a cell's contents, but prevent them from moving, cutting or deleting the cell?

  1. #1
    Dan E
    Guest

    How do i allow users to edit a cell's contents, but prevent them from moving, cutting or deleting the cell?

    I want to prevent users from MOVING, CUTTING or DELETING data cells (which
    would cause any reference to that cell to give an error), but allow them to
    clear the contents of the cell and enter new values (in other words, be able
    to edit the cell). Any suggestions VERY welcome.

    TIA,

    Dan


    --
    Dan E
    webbie(removethis)@preferredcountry.com



  2. #2
    D.2
    Guest

    Re: How do i allow users to edit a cell's contents, but prevent themfrom moving, cutting or deleting the cell?

    NOT SURE this will be enought, but it could be a starter or an idea:
    this allow the double-click action only in the A1 cell: so user can
    edit it (only using the double-click)




    Private Sub Worksheet_BeforeDoubleClick( _
    ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Range("A1")) Is Nothing Then

    ActiveSheet.Unprotect 'editing will be permitted
    Cancel = False 'doubleclick action is maintain

    Else
    protection 'see below, this reactivate the protection

    ' Cancel = True
    'do cancel only if you want to stop the "unprotect sheet first" dialog

    End If
    End Sub


    Private Sub protection()
    ActiveSheet.Protect UserInterfaceOnly:=True
    'the user only can not change the Sheet;VBA still can
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    protection 're-established the protection after any change
    End Sub

    Private Sub Worksheet_Activate()
    protection
    End Sub


  3. #3
    Dan E
    Guest

    Re: How do i allow users to edit a cell's contents, but prevent them from moving, cutting or deleting the cell?

    Thanks, D.2. Does this mean that, if I lock the data cells that I want to
    make editable but not movable, cuttable or deletable, then apply protection
    to the sheet, the locked cells can still be edited by double-clicking? Or
    does it have to be the way you've done it below? I can ALMOST see how your
    subs work, but am something of a novice with VBA syntax, methods etc. - any
    more guidance MUCH appreciated.

    TIA,

    Dan
    "D.2" <Only-News@nomail.fr> wrote in message
    news:%236$CPFwMFHA.3788@tk2msftngp13.phx.gbl...
    > NOT SURE this will be enought, but it could be a starter or an idea:
    > this allow the double-click action only in the A1 cell: so user can edit
    > it (only using the double-click)
    >
    >
    >
    >
    > Private Sub Worksheet_BeforeDoubleClick( _
    > ByVal Target As Range, Cancel As Boolean)
    >
    > If Not Intersect(Target, Range("A1")) Is Nothing Then
    >
    > ActiveSheet.Unprotect 'editing will be permitted
    > Cancel = False 'doubleclick action is maintain
    >
    > Else
    > protection 'see below, this reactivate the protection
    >
    > ' Cancel = True
    > 'do cancel only if you want to stop the "unprotect sheet first" dialog
    >
    > End If
    > End Sub
    >
    >
    > Private Sub protection()
    > ActiveSheet.Protect UserInterfaceOnly:=True
    > 'the user only can not change the Sheet;VBA still can
    > End Sub
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > protection 're-established the protection after any change
    > End Sub
    >
    > Private Sub Worksheet_Activate()
    > protection
    > End Sub
    >




  4. #4
    D.2
    Guest

    Re: How do i allow users to edit a cell's contents, but prevent themfrom moving, cutting or deleting the cell?

    > more guidance MUCH appreciated.


    Well, first we test if this is your special cell:
    IT IS -> you unprotect the sheet, so Excel will allow the edition

    IT IS NOT -> the sheet stay (or is again) protected, so locked cells
    will not be edited

    if you still want to edit unlocked cells, do not use "cancel=true" in
    the ELSE statment: the user will have the error message on (not special)
    locked cells and will edit unlocked ones.


    When a locked cell, but special one, is edited, then the 'Change' sub is
    excecuted: we always re-activate the protection after any change.
    (remember with the special cell we unprotected the sheet, but we want
    the protection back after the change !) So this will take effect with
    any change, for any cell: doesn't matter for unlocked cells, but is
    mandatory after our special cell "unprotection"


    the "activate" sub make the sheet protected at the opening


    if you need more than one special cell, you can Name the cells-zone,
    or add some other cells in VBA: intersect(Target, Union(cel1, cel2, cel3))


  5. #5
    D.2
    Guest

    Re: How do i allow users to edit a cell's contents, but prevent themfrom moving, cutting or deleting the cell?


    because you are a novice: be aware of this fact

    the code must be written in the needed Sheet's code (not in a module,
    neither in the workbook's code) because the events' subs will be called
    only with this sheet...

  6. #6
    Dan E
    Guest

    Re: How do i allow users to edit a cell's contents, but prevent them from moving, cutting or deleting the cell?

    Thank you so much for your patience - I think I understand now. However,
    what I can not see is this - the user simply has the worksheet open - it
    consists of some columns of data cells, any of which may need editing (but
    not deleting, cutting etc.), and hidden between those columns are two
    columns of formulas that refer to the data cells. The formulas are like
    this:-

    =IF(OR(B5="",B5="-"),"-",IF(RIGHT(B5,2)="OR","-",LOOKUP(B5,Crib!$A$1:$A$272,Crib!$B$1:$B$272)))

    - the formula examines the data cell, and gives a numeric result that it
    looks up in a VLOOKUP sheet if the value in the data cell matches one of the
    column A values in the VLOOKUP (Crib). The problem I've been having is that
    the user sometimes cuts and pastes a cell into the location s/he is editing,
    instead of editing the cell's contents. This gives a #REF! error as the
    result of the formula, because the cell (B5 in this case) is no longer
    there. Now, what I can't see is how your macro method can be applied to
    this situation - does the user have to run a macro in order to edit the
    cell, or how does it work practically? Sorry to be dumb!

    TIA,

    Dan
    "D.2" <Only-News@nomail.fr> wrote in message
    news:%23lSgd8wMFHA.3340@TK2MSFTNGP14.phx.gbl...
    >
    > because you are a novice: be aware of this fact
    >
    > the code must be written in the needed Sheet's code (not in a module,
    > neither in the workbook's code) because the events' subs will be called
    > only with this sheet...




  7. #7
    D.2
    Guest

    Re: How do i allow users to edit a cell's contents, but prevent themfrom moving, cutting or deleting the cell?


    At this point maybe you should
    1- read what was said (the user needs to double-click on the locked
    cell he wants to edit)

    2- try to make it (because you need your "first time")



    Just lock the cells you want to protect, protect the sheet, and type in
    the macro in the VBA editor (the code must be associated with the right
    sheet)


    If you realy don't know how the VBA editor works, maybe you should
    read and try with examples (take a look on the web for tutorials) or ask
    a friend... But at this point you should stop asking and start trying...

    this "learning time" is required.

  8. #8
    Dan E
    Guest

    Re: How do i allow users to edit a cell's contents, but prevent them from moving, cutting or deleting the cell?

    OK, thanks, D.2 - your help and advice is much appreciated.

    Thanks again,

    Dan
    "D.2" <Only-News@nomail.fr> wrote in message
    news:u$XwmIyMFHA.4028@tk2msftngp13.phx.gbl...
    >
    > At this point maybe you should
    > 1- read what was said (the user needs to double-click on the locked cell
    > he wants to edit)
    >
    > 2- try to make it (because you need your "first time")
    >
    >
    >
    > Just lock the cells you want to protect, protect the sheet, and type in
    > the macro in the VBA editor (the code must be associated with the right
    > sheet)
    >
    >
    > If you realy don't know how the VBA editor works, maybe you should read
    > and try with examples (take a look on the web for tutorials) or ask a
    > friend... But at this point you should stop asking and start trying...
    >
    > this "learning time" is required.




Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1