Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropdown validation and paste
Hi,
I've inherited an Excel workbook that has a large number of dropdowns used for data validation. The sheet is protected to prevent users from being able to paste values into the workbook. I'd like to allow users to perform a paste operation, and cause the validation associated with each of the drop-down lists to fire so that the paste can be prevented if the data input is invalid. This needs to work on Excel 97 and higher. Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropdown validation and paste
Validation doesn't fire unless you manually enter a value in a cell. So your
basic approach is not supported. Maybe have them do their work on an unprotected duplicate sheet, then validate that input with code and update the protected sheet/provide feedback as appropriate. -- Regards, Tom Ogilvy "chhum" wrote: Hi, I've inherited an Excel workbook that has a large number of dropdowns used for data validation. The sheet is protected to prevent users from being able to paste values into the workbook. I'd like to allow users to perform a paste operation, and cause the validation associated with each of the drop-down lists to fire so that the paste can be prevented if the data input is invalid. This needs to work on Excel 97 and higher. Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropdown validation and paste
Thanks for your reply. How annoying! Is there any way to either: 1) Expressly call the cell validator from the Worksheet_Change event? 2) Access from VBA the range of cells that the validator should be validating against after the cell change event has fired? "Tom Ogilvy" wrote: Validation doesn't fire unless you manually enter a value in a cell. So your basic approach is not supported. Maybe have them do their work on an unprotected duplicate sheet, then validate that input with code and update the protected sheet/provide feedback as appropriate. -- Regards, Tom Ogilvy "chhum" wrote: Hi, I've inherited an Excel workbook that has a large number of dropdowns used for data validation. The sheet is protected to prevent users from being able to paste values into the workbook. I'd like to allow users to perform a paste operation, and cause the validation associated with each of the drop-down lists to fire so that the paste can be prevented if the data input is invalid. This needs to work on Excel 97 and higher. Is this possible? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropdown validation and paste
Well yes, the change event can execute most code. So you can access the
Validated range with code triggered by the change event as long as the user doesn't disable macros overtly or without knowledge because of a high security setting. -- regards, Tom Ogilvy "chhum" wrote in message ... Thanks for your reply. How annoying! Is there any way to either: 1) Expressly call the cell validator from the Worksheet_Change event? 2) Access from VBA the range of cells that the validator should be validating against after the cell change event has fired? "Tom Ogilvy" wrote: Validation doesn't fire unless you manually enter a value in a cell. So your basic approach is not supported. Maybe have them do their work on an unprotected duplicate sheet, then validate that input with code and update the protected sheet/provide feedback as appropriate. -- Regards, Tom Ogilvy "chhum" wrote: Hi, I've inherited an Excel workbook that has a large number of dropdowns used for data validation. The sheet is protected to prevent users from being able to paste values into the workbook. I'd like to allow users to perform a paste operation, and cause the validation associated with each of the drop-down lists to fire so that the paste can be prevented if the data input is invalid. This needs to work on Excel 97 and higher. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation-dropdown | Excel Discussion (Misc queries) | |||
Paste validation, Copy, Paste Special Validation | Excel Programming | |||
How to know if a validation dropdown exist | Excel Programming | |||
dropdown with validation?? | Excel Worksheet Functions | |||
Validation Dropdown List | Excel Programming |