Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do you prevent users from pasting over validated cells?
I have a range of validated cells where the user can only pick values from
the specified list. I don't want them to be able to overwrite these cells with values not in the list by pasting data etc. How do I prevent this? |
#2
|
|||
|
|||
While in your spreadsheet, unlock all those cells where you want to allow
users to enter data. (Format, Cells, Protection, untick "Locked"). Now click on Tools, Protection, Protect Sheet. There are a number of options you can select or deselect. At this stage you can also specify a password to unprotect the sheet, so that only you can do that. Users can then copy data from your protected cells, but cannot overwrite it. Check Excel Help on this "LCK" wrote: I have a range of validated cells where the user can only pick values from the specified list. I don't want them to be able to overwrite these cells with values not in the list by pasting data etc. How do I prevent this? |
#3
|
|||
|
|||
Thanks, but I can't lock cells in the worksheet because the users need to
populate the data for me. I am just trying to excercise a degree of control by only allowing them to enter certain values for particular fields (to avoid free-form text). To do this I have inserted field validation where they have to pick the value from a list. If I locked these cells then they would not be able to pick a value. They cannot enter any values into the cell, besides those defined in my list, but there is a flaw in the validation in that they can copy a value from another cell and paste it over the validated cell - thereby eliminating the validation. How can I prevent the user from passting over these cells, without having to lock the cells? "Kassie" wrote: While in your spreadsheet, unlock all those cells where you want to allow users to enter data. (Format, Cells, Protection, untick "Locked"). Now click on Tools, Protection, Protect Sheet. There are a number of options you can select or deselect. At this stage you can also specify a password to unprotect the sheet, so that only you can do that. Users can then copy data from your protected cells, but cannot overwrite it. Check Excel Help on this "LCK" wrote: I have a range of validated cells where the user can only pick values from the specified list. I don't want them to be able to overwrite these cells with values not in the list by pasting data etc. How do I prevent this? |
#4
|
|||
|
|||
LCK
This is a bit involved so bear with me. The bottom line here is that you need to disable the Paste and Paste Special menu commands whenever the user selects one of the Data Validation cells. This is easy enough to do but it comes with some baggage that you have to take care of. This "baggage" is that disabling a menu command is a global setting in Excel. By this I mean that the new setting (disable so-and-so) applies to Excel, not just to the file that you are working with. In short, if the user selects one of your Data Validation cells, thereby disabling the Paste commands, and then chooses to activate or open another Excel file, that new file will not have the Paste commands available. So you must have code (macros) to do the following: Disable the two Paste commands if a Data Validation cell is selected. Enable the two Paste commands if any other cell is selected. Enable the two Paste commands if any other file is activated or opened. Enable the two Paste commands if your file is closed Select a non Data Validation cell before the file is closed or another file is activated. In the following macros, I chose A1 as a non Data Validation cell. I also chose "TheRng" as the range name of the range of all of your Data Validation cells, so you have to select that range and name it TheRng. I also chose "TheSheetName" as the name of your sheet. I also chose "TheFileName.xls" as the name of your file. You have to change these names in these macros to make them work with your file and sheet. Put the following macro in the sheet module of your sheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("TheRng")) Is Nothing Then Call Enable_Disable_Commands(22, False) 'CANNOT Paste Call Enable_Disable_Commands(755, False) 'CANNOT Paste Special Else Call Enable_Disable_Commands(22, True) 'CAN Paste Call Enable_Disable_Commands(755, True) 'CAN Paste Special End If End Sub Put the following two macros in the Workbook module of your file: Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Enable_Disable_Commands(22, True) 'CAN Paste Call Enable_Disable_Commands(755, True) 'CAN Paste Special Sheets("TheSheetName").Activate Range("A1").Select ThisWorkbook.Save ThisWorkbook.Saved = True End Sub Private Sub Workbook_Deactivate() Call SelectA1 End Sub Put the following two macros in a regular module of your file: Sub Enable_Disable_Commands(id As Integer, Enab As Boolean) Dim myControls As CommandBarControls Dim ctl As CommandBarControl Set myControls = CommandBars.FindControls _ (Type:=msoControlButton, id:=id) For Each ctl In myControls ctl.Enabled = Enab Next ctl End Sub Sub SelectA1() Dim NewFile As String Application.ScreenUpdating = False NewFile = ActiveWorkbook.Name Windows("TheFileName.xls").Activate Sheets("TheSheetName").Activate Range("A1").Select Application.EnableEvents = False Windows(NewFile).Activate Application.EnableEvents = True Application.ScreenUpdating = True End Sub If you send me direct a valid email address for you, I'll send you a small file with all these macros properly placed. My email address is . Remove "nop" from this address. HTH Otto "LCK" wrote in message ... I have a range of validated cells where the user can only pick values from the specified list. I don't want them to be able to overwrite these cells with values not in the list by pasting data etc. How do I prevent this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I prevent users to go into locked cells | Excel Discussion (Misc queries) | |||
how do I prevent users to go into locked cells | Excel Discussion (Misc queries) | |||
When Sharing settings dont appear the same for all users | Excel Discussion (Misc queries) | |||
formatting when pasting into excel | Excel Discussion (Misc queries) | |||
Excel prompt inaccessible to users | Excel Discussion (Misc queries) |