Dear Narayan, My sincere apologies again that I did not answer your email. Actually I get a notification on my gmail account if some one replies to my postings. But on this occasion I did not get it, and so I missed replying you.
first up, thanks again for your courtesy. From your reply above, quote "now I understand why it is necessary to clear the contents of the cell in column D" unquote, I actually do not want to Clear the contents of cell D, I want the user to be prevented from using this instruction (right click selection of Row and clear content or right selection of cell D and clear content) because the 'clear content' will not trigger the event. Please go through below lines, I have tried to explain in more details how the code is expected to work.
MasterList! has 8 columns A: PID reference, B: Service Area, C: Service Equipment, D: Instrument_Type, E: Instrument, F: Tag_No., G: Suffix, H: RedLvl. All Cols except G and H are Drop Down Lists. G and H are formula based. The event based code belongs to this wsh. Event changes are performed in Cell D. When a data from drop down list is selected in Cell D, Cell E border changes to Red, prompting the user to select the data into Cell E from its own drop down list. Changes in Cell D trigger an event. This proceeds as below:
When entries in Cell D and Cell E are made, a range gets copied from a source sheet to a destination sheet.
The Range, Source Sheet and Destination sheets along with offsets are mentioned in the wsh List!
As the rows in the MasterList! get populated, the appropriate ranges are copied in appropriate sheets along with the tag numbers. The Tag_No gives an exact identification of which range is copied. The Tag_No is unique to every row that is entered in the MasterList! and it is also unique to every copy that is effected in the destination sheets. If the same selections are made for every drop down list in different rows, then the suffixes are added in column G. The ampersand of Tag_No and Suffix is copied as a unique number in the destination wsh.
The application becomes more complex when a certain Row which was entered, is to be deleted. In this situation, the Range which was copied due to the current value of the data in cell D must be the Range which needs to be deleted. The other serious complexity that is involved is just by merely deleting a row or clearing the contents of a row or clearing the contents in Cell D in the MasterList! does not delete the range that was copied in the particular destination. Therefore if the content in cell D is deleted by using the Delete Button on the keyboard, this act of change in Cell D triggers an event that deletes the correct range in the correct destination that was copied due to the current value of the Cell D. It would be perhaps required to avoid using “Clear Contents” by right clicking the particular row, by graying out “Clear Contents” when the row selection is made by using right click. This is only a suggestion and it is not important as to what method is followed to achieve this functionality. The coder may also create Buttons which can do this function.
The user may also just change the current value of cell D (instead of deleting) by selecting a new value. But the effect of this action is same as deleting the current range in a particular destination and inserting a new range in a new destination.
Your file test_modified-1 performs almost exactly as what is described above, except that it allows “Selection of Row by Right Click and using ‘clear contents’ and also allows selection of cell D by right click and clear content.
And therefore when "clear content" is used to clear contents in the row or the Cell D, it does not delete the range which was earlier copied.
continuing with my sincere thanks and with best regards
Vilas Desai
