no move and paste
no move and paste
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: no move and paste

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Hong Kong
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a worksheet with many formulas. Can I restrict the users from moving and pasting cells (paste values only is allowed)? I can only lock cells with formulas. Cells for data input must be unlocked.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aries

    All cells are Locked by default, but this has no effect until the sheet is protected, Tools>Protection>Protect Sheet.

    The cells you want to allow data entry into need to be Unlocked via the Format dialog.

    Simple way is this.

    Select any single cell
    Push F5 and click Special
    Select "Constants"
    Click Ok
    Go to Format>cells>Protection.



  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Hong Kong
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I don't understand your point.
    I want to prevent the users from moving the cells for data input as it will change the address in the formulas in other cells.
    I am not familiar with the goto (F5) function, how can I acheive my goal with it?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another way to Format the cells is to:

    Select the cells you want to unlock (or allow your users to modify, such as data input cells)
    Right Click and select Format Cells
    Click on the Protection Tab
    Uncheck the box marked Locked
    Click OK

    Now protect the sheet as normal and the only cells your users will be able to change are the ones you unlocked

    [ This Message was edited by: Audiojoe on 2002-03-20 02:34 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Hong Kong
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I know how to unlock the cells.
    But what I want to do is:
    I have a rectangular range of cells unlocked. Users can input data into the cells. But if they mistakenly input data into wrong cell (say the next column, which is also an unlocked cell), they tries to move the cells to the correct address. I want to prohibit these moves.

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    The formala reference will only change if they "Cut" the cell. This is by design if you prevent this from happening the consqueces may be worse! However if this is what you want there is one non VBA approach I can think of. This is to enclose all cell references in your formulas with the INDIRECT function
    =INDIRECT("E1")



User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com