+ Reply to Thread
Results 1 to 8 of 8

Locking columns

  1. #1
    Registered User
    Join Date
    07-16-2007
    Posts
    7

    Locking columns

    How can I lock the values that can be entered into a column ?. I have used "data" "validation" to set lists of valid values that can be entered into columns by using drop down boxes. But I need to stop people from entering valid values into one column and then cutting and pasting this data into another column where those copied values may no longer be valid.

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    Have you tried protecting the worksheet? If you're not familiar with this, select all of the columns where you do allow users to enter data, then right click and select Format Cells, Protection and untick the Locked option. Return to the worksheet and click on Tools, Protection, Protect Worksheet.
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

  3. #3
    Registered User
    Join Date
    07-16-2007
    Posts
    7

    Smile Locking columns

    Thanks very much for info. I've just tried your suggestion, but it doesnt appear to work. Its still possible to copy and paste values between columns. I need to be able to enter valid data in the columns but not copy and paste from one column to another taking across values that might not valid values in the column where they are being pasted.

    I'd be grateful for any more suggestions?

  4. #4
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    Unprotect the worksheet, then protect it again. This time, though, look through the list of tick options you can set to control what the users can and cannot do in locked cells. In particular, if you untick Select Locked Cells, they can't click on any of those cells to paste.

  5. #5
    Registered User
    Join Date
    07-16-2007
    Posts
    7

    Locking columns

    Thanks, still no luck. This stops unwanted copying and pasting from column to column but also stops users from entering any data at all!!

    It seems that I can either the lock cells in which case data cant be entered or moved at all, or unlock cells in which case anything can be copied and pasted across columns.

    Thanks

  6. #6
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    Quote Originally Posted by helen_spencer9
    Thanks, still no luck. This stops unwanted copying and pasting from column to column but also stops users from entering any data at all!!

    It seems that I can either the lock cells in which case data cant be entered or moved at all, or unlock cells in which case anything can be copied and pasted across columns.

    Thanks
    I assume that the user is entering data in one column, say column E, then copying and pasting it into another column, say column F. In this case, you need to unlock column E and lock column F before protecting the worksheet.

  7. #7
    Registered User
    Join Date
    07-16-2007
    Posts
    7

    Locking columns

    I have for example 2 columns A and B into which users must enter their data. In column A the responses allowed are "yes", "no" and in column B the responses allowed are "once a week", "twice week". Data is entered by having drop down boxes in each column which users click on.

    The problem is that some users in the past have entered data correctly into A, and then somehow accidentally copied and pasted the contents of A into B, rather than entering data into B manually using the drop down boxes. So the values they entered in B are then "yes", "no" which they shouldnt be. This causes later computations to be wrong.

    I need to allow them to enter data into both A and B, but they must be the correct values for that column. They must not be able to copy the contents from A to B making B incorrect.

    I dont seem to be able to allow users to enter data, but make sure they enter only data in the range allowed. It seems I either lock cells in which case they cant enter any data or unlock cells in which case they can corrupt them.

    Thanks very much !

  8. #8
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    I have for example 2 columns A and B into which users must enter their data. In column A the responses allowed are "yes", "no" and in column B the responses allowed are "once a week", "twice week". Data is entered by having drop down boxes in each column which users click on.

    The problem is that some users in the past have entered data correctly into A, and then somehow accidentally copied and pasted the contents of A into B, rather than entering data into B manually using the drop down boxes. So the values they entered in B are then "yes", "no" which they shouldnt be. This causes later computations to be wrong.

    I need to allow them to enter data into both A and B, but they must be the correct values for that column. They must not be able to copy the contents from A to B making B incorrect.

    I dont seem to be able to allow users to enter data, but make sure they enter only data in the range allowed. It seems I either lock cells in which case they cant enter any data or unlock cells in which case they can corrupt them.
    I get the picture. This is beyond my level of expertise, I'm afraid. It might be worthwhile submitting a new thread with this detailed information so that someone with more expertise will offer some advice.

+ Reply to 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