| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect all cells but allow data validation dropdown change
I have a file that I want to protect all cells. But at the same time there
are certain cells that have a Data Validation Dropdown where I want the user to be able to select an item off the dropdown list and update the cell. I want to protect everything because I do not want anyone to be able to "Paste" anything in the file or make any changes in the file except for making selections from the Data Validation Dropdown and no other way. For example: The cells that have dropdowns must be in protected mode when user enters the cell because I do not want the user to be able to take a cell from somewhere else and copy / paste over the Data Validation Cell I have setup. Therefore I need the protection as mentioned above. How can I accomplish this? Thank you for your help. Steven |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protect all cells but allow data validation dropdown change
On Jun 14, 7:59 am, Steven wrote:
I have a file that I want to protect all cells. But at the same time there are certain cells that have a Data Validation Dropdown where I want the user to be able to select an item off the dropdown list and update the cell. I want to protect everything because I do not want anyone to be able to "Paste" anything in the file or make any changes in the file except for making selections from the Data Validation Dropdown and no other way. For example: The cells that have dropdowns must be in protected mode when user enters the cell because I do not want the user to be able to take a cell from somewhere else and copy / paste over the Data Validation Cell I have setup. Therefore I need the protection as mentioned above. How can I accomplish this? Thank you for your help. Steven Hi Steven, AFAIK you need to use VBA to prevent loss of validation caused by pasting. Before a user can paste over the validated cell they have to select it. The Worksheet_SelectionChange event with Application.CutCopyMode = True (or Application.CutCopyMode = False) can then stop the user pasting. For example, say the cell with the validation dropdown is Sheet1!A1, then Sheet1's code module could be... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Application.CutCopyMode = True End If End Sub This on its own will only work when the user copies from the same sheet as the validated cell, that which he/she intends pasting. The user could select the validated cell (A1), switch to a different sheet, copy, switch back to the sheet with the validated cell already selected, then paste and destroy the validation. The Worksheet_SelectionChange would not be triggered to prevent the pasting. So, the Worksheet_Activate event also has to be used to change the selection if it includes the validated cell... Private Sub Worksheet_Activate() If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End Sub Similarly, to cover the pasting of stuff copied from another workbook, the Workbook_Activate event in the ThisWorkbook code module has also to be used... Private Sub Workbook_Activate() If ActiveSheet.Name = "Sheet1" Then If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End If End Sub Ken Johnson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protect all cells but allow data validation dropdown change
Ken,
Thank you for the help. One more issue. What if the person opens another session of excel and copies out of that session of excel and switches back and pastes. How do you catch that? Thank you, Steven "Ken Johnson" wrote: On Jun 14, 7:59 am, Steven wrote: I have a file that I want to protect all cells. But at the same time there are certain cells that have a Data Validation Dropdown where I want the user to be able to select an item off the dropdown list and update the cell. I want to protect everything because I do not want anyone to be able to "Paste" anything in the file or make any changes in the file except for making selections from the Data Validation Dropdown and no other way. For example: The cells that have dropdowns must be in protected mode when user enters the cell because I do not want the user to be able to take a cell from somewhere else and copy / paste over the Data Validation Cell I have setup. Therefore I need the protection as mentioned above. How can I accomplish this? Thank you for your help. Steven Hi Steven, AFAIK you need to use VBA to prevent loss of validation caused by pasting. Before a user can paste over the validated cell they have to select it. The Worksheet_SelectionChange event with Application.CutCopyMode = True (or Application.CutCopyMode = False) can then stop the user pasting. For example, say the cell with the validation dropdown is Sheet1!A1, then Sheet1's code module could be... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Application.CutCopyMode = True End If End Sub This on its own will only work when the user copies from the same sheet as the validated cell, that which he/she intends pasting. The user could select the validated cell (A1), switch to a different sheet, copy, switch back to the sheet with the validated cell already selected, then paste and destroy the validation. The Worksheet_SelectionChange would not be triggered to prevent the pasting. So, the Worksheet_Activate event also has to be used to change the selection if it includes the validated cell... Private Sub Worksheet_Activate() If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End Sub Similarly, to cover the pasting of stuff copied from another workbook, the Workbook_Activate event in the ThisWorkbook code module has also to be used... Private Sub Workbook_Activate() If ActiveSheet.Name = "Sheet1" Then If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End If End Sub Ken Johnson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protect all cells but allow data validation dropdown change
On Jun 15, 5:00 am, Steven wrote:
Ken, Thank you for the help. One more issue. What if the person opens another session of excel and copies out of that session of excel and switches back and pastes. How do you catch that? Thank you, Steven "Ken Johnson" wrote: On Jun 14, 7:59 am, Steven wrote: I have a file that I want to protect all cells. But at the same time there are certain cells that have a Data Validation Dropdown where I want the user to be able to select an item off the dropdown list and update the cell. I want to protect everything because I do not want anyone to be able to "Paste" anything in the file or make any changes in the file except for making selections from the Data Validation Dropdown and no other way. For example: The cells that have dropdowns must be in protected mode when user enters the cell because I do not want the user to be able to take a cell from somewhere else and copy / paste over the Data Validation Cell I have setup. Therefore I need the protection as mentioned above. How can I accomplish this? Thank you for your help. Steven Hi Steven, AFAIK you need to use VBA to prevent loss of validation caused by pasting. Before a user can paste over the validated cell they have to select it. The Worksheet_SelectionChange event with Application.CutCopyMode = True (or Application.CutCopyMode = False) can then stop the user pasting. For example, say the cell with the validation dropdown is Sheet1!A1, then Sheet1's code module could be... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Application.CutCopyMode = True End If End Sub This on its own will only work when the user copies from the same sheet as the validated cell, that which he/she intends pasting. The user could select the validated cell (A1), switch to a different sheet, copy, switch back to the sheet with the validated cell already selected, then paste and destroy the validation. The Worksheet_SelectionChange would not be triggered to prevent the pasting. So, the Worksheet_Activate event also has to be used to change the selection if it includes the validated cell... Private Sub Worksheet_Activate() If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End Sub Similarly, to cover the pasting of stuff copied from another workbook, the Workbook_Activate event in the ThisWorkbook code module has also to be used... Private Sub Workbook_Activate() If ActiveSheet.Name = "Sheet1" Then If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End If End Sub Ken Johnson Hi Steven, Maybe I misunderstand the scenario you described, but doesn't the user still have to activate the workbook with the validated cell before he/ she can paste, and this activation changes the selection so that he/ she then has to reselect the validated cell, triggering the SelectionChange sub that prevents the paste. Have you been able to override the validation the way you described? Of course one other way the user can override the validation is to NOT Enable Macros when opening the workbook. Then you would have to set up your workbook so that when it is opened without Enabled Macros all sheets bar one have their Visible property set at xlVeryHidden. The remaining visible sheet can carry a message stating that the macros are a vital part of the workbook and that the user should Close then re-Open and Enable Macros. Ken Johnson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protect all cells but allow data validation dropdown change
Ken,
Thank you for responding again. My experience in testing this is where a user opens a second session of excel. That is not a new workbook in the same session but actually starts another application session (now we have two sessions of excel running). I am going to say something here that you already know but I just want to be clear. I have started Session One of excel and open files File001.xls ; File002.xls ; File003.xls and then open another session "Session Two" of excel and open files File004.xls ; File005.xls ; File006.xls . Then if I am hitting CTRL+TAB and I am in the Session Two then I will cycle files 4, 5 and 6 ; but not 1, 2 and 3 because they are in a completely different session. Now if I am in Session One File002.xls and Copy and go to File001.xls to paste.. yes ... it will disable the paste because the Workkbook_Activate subroutine ran. But if I am in File001.xls and then click on the taskbar to Session Two to File004.xls and Copy and click on the taskbar back to Session One and do not move the cursor, because I was being sneaky and already had the cursor where I wanted it, then I am able to paste because none of the events were triggered in this case. Am I correct? Am I missing something here or is there still another catch to be made. Thank you very much for your help. Steven "Ken Johnson" wrote: On Jun 15, 5:00 am, Steven wrote: Ken, Thank you for the help. One more issue. What if the person opens another session of excel and copies out of that session of excel and switches back and pastes. How do you catch that? Thank you, Steven "Ken Johnson" wrote: On Jun 14, 7:59 am, Steven wrote: I have a file that I want to protect all cells. But at the same time there are certain cells that have a Data Validation Dropdown where I want the user to be able to select an item off the dropdown list and update the cell. I want to protect everything because I do not want anyone to be able to "Paste" anything in the file or make any changes in the file except for making selections from the Data Validation Dropdown and no other way. For example: The cells that have dropdowns must be in protected mode when user enters the cell because I do not want the user to be able to take a cell from somewhere else and copy / paste over the Data Validation Cell I have setup. Therefore I need the protection as mentioned above. How can I accomplish this? Thank you for your help. Steven Hi Steven, AFAIK you need to use VBA to prevent loss of validation caused by pasting. Before a user can paste over the validated cell they have to select it. The Worksheet_SelectionChange event with Application.CutCopyMode = True (or Application.CutCopyMode = False) can then stop the user pasting. For example, say the cell with the validation dropdown is Sheet1!A1, then Sheet1's code module could be... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Application.CutCopyMode = True End If End Sub This on its own will only work when the user copies from the same sheet as the validated cell, that which he/she intends pasting. The user could select the validated cell (A1), switch to a different sheet, copy, switch back to the sheet with the validated cell already selected, then paste and destroy the validation. The Worksheet_SelectionChange would not be triggered to prevent the pasting. So, the Worksheet_Activate event also has to be used to change the selection if it includes the validated cell... Private Sub Worksheet_Activate() If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End Sub Similarly, to cover the pasting of stuff copied from another workbook, the Workbook_Activate event in the ThisWorkbook code module has also to be used... Private Sub Workbook_Activate() If ActiveSheet.Name = "Sheet1" Then If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End If End Sub Ken Johnson Hi Steven, Maybe I misunderstand the scenario you described, but doesn't the user still have to activate the workbook with the validated cell before he/ she can paste, and this activation changes the selection so that he/ she then has to reselect the validated cell, triggering the SelectionChange sub that prevents the paste. Have you been able to override the validation the way you described? Of course one other way the user can override the validation is to NOT Enable Macros when opening the workbook. Then you would have to set up your workbook so that when it is opened without Enabled Macros all sheets bar one have their Visible property set at xlVeryHidden. The remaining visible sheet can carry a message stating that the macros are a vital part of the workbook and that the user should Close then re-Open and Enable Macros. Ken Johnson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protect all cells but allow data validation dropdown change
On Jun 16, 2:28 am, Steven wrote:
Ken, Thank you for responding again. My experience in testing this is where a user opens a second session of excel. That is not a new workbook in the same session but actually starts another application session (now we have two sessions of excel running). I am going to say something here that you already know but I just want to be clear. I have started Session One of excel and open files File001.xls ; File002.xls ; File003.xls and then open another session "Session Two" of excel and open files File004.xls ; File005.xls ; File006.xls . Then if I am hitting CTRL+TAB and I am in the Session Two then I will cycle files 4, 5 and 6 ; but not 1, 2 and 3 because they are in a completely different session. Now if I am in Session One File002.xls and Copy and go to File001.xls to paste.. yes ... it will disable the paste because the Workkbook_Activate subroutine ran. But if I am in File001.xls and then click on the taskbar to Session Two to File004.xls and Copy and click on the taskbar back to Session One and do not move the cursor, because I was being sneaky and already had the cursor where I wanted it, then I am able to paste because none of the events were triggered in this case. Am I correct? Am I missing something here or is there still another catch to be made. Thank you very much for your help. Steven "Ken Johnson" wrote: On Jun 15, 5:00 am, Steven wrote: Ken, Thank you for the help. One more issue. What if the person opens another session of excel and copies out of that session of excel and switches back and pastes. How do you catch that? Thank you, Steven "Ken Johnson" wrote: On Jun 14, 7:59 am, Steven wrote: I have a file that I want to protect all cells. But at the same time there are certain cells that have a Data Validation Dropdown where I want the user to be able to select an item off the dropdown list and update the cell. I want to protect everything because I do not want anyone to be able to "Paste" anything in the file or make any changes in the file except for making selections from the Data Validation Dropdown and no other way. For example: The cells that have dropdowns must be in protected mode when user enters the cell because I do not want the user to be able to take a cell from somewhere else and copy / paste over the Data Validation Cell I have setup. Therefore I need the protection as mentioned above. How can I accomplish this? Thank you for your help. Steven Hi Steven, AFAIK you need to use VBA to prevent loss of validation caused by pasting. Before a user can paste over the validated cell they have to select it. The Worksheet_SelectionChange event with Application.CutCopyMode = True (or Application.CutCopyMode = False) can then stop the user pasting. For example, say the cell with the validation dropdown is Sheet1!A1, then Sheet1's code module could be... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Application.CutCopyMode = True End If End Sub This on its own will only work when the user copies from the same sheet as the validated cell, that which he/she intends pasting. The user could select the validated cell (A1), switch to a different sheet, copy, switch back to the sheet with the validated cell already selected, then paste and destroy the validation. The Worksheet_SelectionChange would not be triggered to prevent the pasting. So, the Worksheet_Activate event also has to be used to change the selection if it includes the validated cell... Private Sub Worksheet_Activate() If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End Sub Similarly, to cover the pasting of stuff copied from another workbook, the Workbook_Activate event in the ThisWorkbook code module has also to be used... Private Sub Workbook_Activate() If ActiveSheet.Name = "Sheet1" Then If Not Intersect(Range("A1"), Selection) Is Nothing Then 'Force reselection of A1 to trigger SelectionChange sub Range("B1").Select 'any cell other than the validated one End If End If End Sub Ken Johnson Hi Steven, Maybe I misunderstand the scenario you described, but doesn't the user still have to activate the workbook with the validated cell before he/ she can paste, and this activation changes the selection so that he/ she then has to reselect the validated cell, triggering the SelectionChange sub that prevents the paste. Have you been able to override the validation the way you described? Of course one other way the user can override the validation is to NOT Enable Macros when opening the workbook. Then you would have to set up your workbook so that when it is opened without Enabled Macros all sheets bar one have their Visible property set at xlVeryHidden. The remaining visible sheet can carry a message stating that the macros are a vital part of the workbook and that the user should Close then re-Open and Enable Macros. Ken Johnson Hi Steven, It looks to me like you have found a hole that can't be filled! Switching between different sessions does not trigger any of the event procedures. Perhaps a completely different approach is called for. After the pasting has been done the Worksheet_Change event is triggered so maybe you could use VBA to repair the damage after it has occurred. What are the details of your validation? I must admit to being a little confused. I always thought that pasting over a validated cell destroyed the validation. After a little experimenting that seems not to be the case. When I copied a cell with one type of validation then pasted into a different cell with a completely different type of validation, its (the cell being pasted into) validation did not change, but the unallowed pasted value was allowed. So, maybe all that the VBA code has to do is back up the validation. What are your thought? Ken Johnson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data validation with dropdown list | Excel Discussion (Misc queries) | |||
| Dropdown lists from datavalidation. | Excel Discussion (Misc queries) | |||
| Protect Data validation cells from copy paste | Excel Discussion (Misc queries) | |||
| Data Validation Dropdown bug | Excel Programming | |||
| Data Validation Calender Dropdown | Excel Programming | |||
Hybrid Mode
