Multiple Selection Data Validation and Placement of Selection.

Hi

Getting a bit lost here.
If you select welder, and the offset column is blank, then put in welder.

If you again select welder (either the same cell, or another one in column 10) the put welder in the last row of the offset column (this is what your code is doing - is that right, or should you be adding it to the next blank row???).

If you select welder again (ie a 3rd selection of welder), then you want to remove the second instance of welder? Or do you just want to make sure that there are a maximum of 2 "welder" in the offset column?

rylo

Thanks for your reply,

Let me explain the workhourse of my code and see if that helps

Please Login or Register  to view this content.

Select case refers to the column in which the data validation exists, in the case, column 10.

Target.Value is the cell in which the data validation exists.

The program first checks to see if cell in the same row and 18 columns over, if it is blank, it will put the target value (data validation drop down) in cell that is in the same row and 18 columns over.

If there is a character in that cell, it then finds the last cell in the column with a character in it and looks at the cell below that and indexes it as the object lRow. Then, it puts the target.value (data validation drop down value) in the open cell in the column 18 columns to the right of the data validation cell.

Thus, if I keep selecting welder, it will add welder down the list forever. (Theoretically, every entry in the entire column could be "welder") My code works for what it does. However, I would like to only have one instance of welder allowed. If I were to choose welder a second time, I would like it to erase the already existing instance of welder.

e.g. If welder exists in cell U2, and I select welder in cell A2 (data validation cell), then, U2 would become blank. I hope this clarifies a little bit.

Thanks for your time,

Lucas

Lucas

Couple of things,
lRow = Cells(Rows.count, lCol + 18).End(xlUp).Row
finds the last used cell in the relevant column and bring back that row, not the row below it. So the last used cell would be filled, not the next black cell in the column.

Steps would be
Enter welder in A2, Nothing in U2, so enter welder in U2
Enter welder in A2, U2 has welder, so how is it to know to put in a second welder in the next available cell in column U (Say U10), or to delete the exising one in U2?
Enter welder in A3. Say U3 has builder, it will then add welder to U11
Enter welder in A3. U3 still has builder, so it will add welder to U12. And so on.

What exactly do you want to happen?

rylo

rylo,

Sorry about my mistake,

lRow = Cells(Rows.count, lCol + 18).End(xlUp).Row should be:

lRow = Cells(Rows.count, lCol + 18).End(xlUp).Row+1

I apologize. So every time a new value is chosen in A2, it places it in the first open row in column U. Keep in mind, nothing is being deleted at this point (you probably already know that), so it just becomes one gigantic list.

I want it to: if I select "welder", it will search column U and if "welder" exists, then, it would delete it from the column U.

If it does not exist, then I would like it to place it in the first open cell. (I already see a flaw in that I trying to work some code in the first ELSE portion) However, I am still very uncertain as how I would write the searching mechanism/encorporate it into my code.

Thanks for your patience with my lack of programming/writing skills tonight.

Lucas

Lucas

Lets see. Make a selection in A2. If that selection already exists in column U, then delete it. If it doesn't exist, then append to column U. There is to be a maximum of 1 instance of any selection. Then what are you going to do with the blank cells in column U? Say there is an instance in U5, and you select that item in A2. It will then clear out U5. Does this just continue to remain a blank cell? Do you want to sort column U so there are no blanks?

rylo

rylo,

Lets see. Make a selection in A2. If that selection already exists in column U, then delete it. If it doesn't exist, then append to column U. There is to be a maximum of 1 instance of any selection.

That is what i would like it to do, but have no clue how the VBA code would look. column U will be hidden as it will be used to be a criteria for an advanced filter on a separate sheet. The data validation column will be for a user who is not familiar at all with excel. This will allow him to choose multiple selections to get a result.

Then what are you going to do with the blank cells in column U? Say there is an instance in U5, and you select that item in A2. It will then clear out U5. Does this just continue to remain a blank cell? Do you want to sort column U so there are no blanks?

If possible, I would prefer if I could get the code to remove the cell so there were no blanks existing.

Thanks again,
Lucas

Lucas

Have a look at the very simple attachment. Make a change in A2, and it will action in column U. If you don't have an entry in column U, it will add it. If it already exists, then it will delete it. This does seem a bit silly as if there is an entry that you want to retain, then it will be deleted so none exists and you would have to know that it has deleted an entry and then reenter it.

But hopefully it will give some construct ideas.

LCS.xlsm

rylo

rylo,

Thank you! That's what I wanted it to do. You are correct, very simple but useful code. I might have been thinking too hard.

Lucas