Help request to add automatic dv in target row

[mso3]

Hi friends,

I want to create a dynamic dv on ‘Data’ sheet. The list for dv list is on ‘Support’ sheet. I have created the names in name manager. I want to create/add dv in the target row if the user enters a inward number in column ‘B’ from row 2. I have a code in sheet module of ‘Sheet2’ for your kind perusal which creates a auto dv in cell ‘D1’ and indirect dv in cell ‘E1’. I want to modify/amend it to meet my requirements as follows:

If the user enters a inward number in column then …
Add dv in target row column ‘D’ for ‘Item’
Add indirect dv in target row column ‘E’ for ‘Particulars’
Add dv in target row column ‘F’ for ‘Designation’
Add dv in target row column ‘J’ for ‘Mode of payment’
Add dv in column ‘M’ for ‘Name of bank’

If the user deletes the inward number in column ‘B’ then …
Clear these dv in target row

I request you experts to assist me to achieve the target positively.

Any help will be highly appreciated.

Thanking you in anticipation.

[southward]

Hi,

I only have time right now to direct help regarding the "Add indirect dv in target row column ‘E’ for ‘Particulars’".

This website will show you how.

http://www.contextures.com/xlDataVal02.html

Cheers

[mso3]

Hi,

Thank you. I had gone through the link you provided but found no solution. I have no problem in creating dv list manually. I don’t want to add dv list unnecessarily to the entire range of database; for ex. If the range of dv column is from B2:B5000 then there is a dv list for the entire range though most of the rows are not used for data entry, no any entry in that row. I want to avoide it using target row so that there will not be the dv list for the entire range. So I request you to suggest me the vba code for sheet module.

Thanking you,

[mso3]

Post deleted.

[mso3]

Hi,

Greetings from me!

I have amended the code as follows:

Please Login or Register  to view this content.

I'm not getting indirect dv list in column 'E'.
If the user deletes the value in column 'B' then I want to clear all dv in target row.
Thanking you.

[Naveed Raza]

HI mso,

1. if user delete the value in column B then you want to delete complete row it self or just respective column (target row) value which has dv that cell you want to delete
2. if user delete try to delete multiple value from column B then we need to write loop to delete dv

[mso3]

Hi Naveed,

Greetings from me!

Thank you. I have amended the code in the attached workbook.

If the user deletes the inward number in column ‘B’ then I want to delete validation and contents of target row range ‘C:V’ respectively. I tried to do it but inn vain.

The second main problem is that I’m not getting indirect dv list in column ‘E’. It’s referring the second row ‘$D2’ of column ‘D’ only in all rows.

Thanking you,

[Naveed Raza]

Hi Mso3

did you get any solution ? if not will post the solution in next couple of hours as currently i am working on monthly report.

[mso3]

Hi Naveed,

First of all I by heart appreciate you for your concern about my problem.
No, I tried my level best to solve the problem but in vain. I amended the code but getting run-time error 1004 application-defined or object-defined error for the dv of column ‘E’.

Since last 2-3 days I’m working on it but no success. I’m optimistic to receive a positive concrete solution from you soon. Please see the attached workbook for amended code.

Thank you and have a nice time.

[Naveed Raza]

Hi Mso3

need small clarification, if user delete any one value from these columns (desgination, MOP & Name of bank) then validation plus value need to delete or nothing should happen

[Naveed Raza]

Hi Mso,

please find attached file and let us know is it what you are looking for and hope this will work perfectly

[mso3]

Hi Naveed,

Excellent! Thank you. Now the indirect dv is working fine. To complete the project I require little amendments in the code as follows:

There is another code in sheet module. I have used ‘option explicit’ there. After adding this code to it I’m getting error ‘variable not define’. Can we define the variable to avoide this error?

I’m not getting warning message ‘Please Enter valid Inward number’ if without entering the inward number in column the user start entering in range ‘c:v’.

If there is no inward number then the user should not enter anything in range ‘c:v’. Here I require a warning message which you have set but it’s not working.

If there is a inward number in column ‘B’ then the user can edit the range ‘c:v’ but can’t delete anything. If he want to delete anything in range row then he has to delete the inward number so that the entire range ‘b:v’ will be deleted.

If the user deletes the inward number in column then I require a warning message ‘Do you want to delete the entry?’ if yes then delete; if no then exit sub. I have set it at the end of the code in my attached workbook post9.

Everything else is fine.

Thank you.

[Naveed Raza]

HI mso

okay , let me check these

please allow me some time i will look for these

[Naveed Raza]

Hi Mso,

please find updated version of file and let me know

[mso3]

Hi Naveed,

Good morning!

Thank you for updated version. Now some of the problems are solved but still need little amendments as follows:

If the user deletes a inward number in column ‘B’ then I require a warning message to prevent accidentally deletion of entry as follows:

Please Login or Register  to view this content.

If the user changed the value in dropdown list in column ‘D’ then the value in column ‘E’ should be deleted to avoide mismatch. Otherwise there will be color in column ‘D’ and name of flower in column ‘E’ for example.

If there is a inward number in column ‘B’ then the user is not allowed to delete the data in range ‘c:v’ only; now it’s beyond the range. I don’t want it from column ‘W’ onward because there is another data in my original workbook. Please restrict it till column ‘v’.

If the user overwrites the inward number in column ‘B’ then I require a warning message as follows:
“Do you want to overwrite the inward number?” Yes/No
If ‘Yes’ then delete entire range ‘B:V’
If ‘No’ then undo exit the sub.
It will prevent accidental deletion of entry.

For variable problem I set the variable as follows:

Please Login or Register  to view this content.

What does ‘tn’ refer? Is it correct?
It solved the variable not defined problem.

Everything else is perfect as per requirement. I appreciate you for taking trouble to solve the problem. After these amendments the project will be completed today.

Sorry for trouble.

Thank you and have a nice day.

[Naveed Raza]

HI mso,

if user try to enter existing inward number then you want pop up right "inward already exists do you want to enter overwrite , yes/no" if user click on yes then previous existing entry should be delete (entire row) right or if user click on No then user not able to enter new value.

is it correct ?

[mso3]

Hi Naveed,

No, I don't mean duplicate entry restriction. I have already a code to prevent inward number in column. So that the user can't enter a duplicate inward number.

I mean ..
Suppose the user entered 123 inward number and next time if he tries to enter a different inward number on the same row then I require a warning message mentioned my post to prevent mistaken deletion of previous entry.

if the user enter the next entry of inward number 125 on the same row inward number 123 then I require a warning message if the user really wants to overwrite the entry or it's his mistake he is overwriting previous entry.

[Naveed Raza]

Hi mso,

please find attached final version of file.

i have implemented all the requirement , please have test and let me know

[mso3]

Hi Naveed,

Excellent! Now this version is perfect. I appreciate you for the same.

I require input message, error alert message, error title in the dv.
I have shown the require output for example for the Item column ‘D’. Likewise for each validation I will add the input message.
Thank you.

[Naveed Raza]

Hi mso3,

please find attached updated file

[mso3]

Hi Naveed,

Excellent! I appreciate you for your kind cooperation to solve the problem. Now it’s 100% perfect as per the requirement.

Wish you best of luck.

Thank you and good night.