Multiple Selection Data Validation and Placement of Selection.

**LCS** · 02-07-2012, 02:47 PM

I have a program in excel that allows me to do analysis/summarize data on one form by searching for terms through the use of drop down lists using data validation. I would like to be able to select multiple factors and have those selections inserted into a different column with each selection in a different row. I am able to do that with the following code adapted from the contextures website:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
Dim cRow As Long

lCol = Target.Column 'column with data validation cell

If Target.count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
    If Target.Value = "" Then GoTo exitHandler
    Application.EnableEvents = False
    Select Case Target.Column
       Case 10
        If Target.Offset(0, 18).Value = "" Then
           lRow = Target.Row
        Else
            lRow = Cells(Rows.count, lCol + 18).End(xlUp).Row
    End If
            Cells(lRow, lCol + 18).Value = Target.Value
    End Select
End If

exitHandler:
  Application.EnableEvents = True
  
End Sub

My data validation list consists of jobs: Welder, Mechanic, and Janitor. (and many other fields)
If Welder is selected, this places Welder in another column (column 18). If it is selected again, then it gets inserted into the same column and row below it. My problem is, if I were to select Welder twice, I would like it to remove the "Welder" from the column, rather than placing another instance of it in there.

I have a decent amount of experience coding in R, but very little in VBA, and writing loops are very foreign for me. I have a general strategy how I might approach this sort of problem in R, but it will do me no good in there

. Thanks in advance for your time,

Lucas Smith

**rylo** · 02-07-2012, 06:35 PM

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

**LCS** · 02-07-2012, 06:57 PM

Thanks for your reply,

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

Select Case Target.Column
       Case 10
        If Target.Offset(0, 18).Value = "" Then
           lRow = Target.Row
        Else
            lRow = Cells(Rows.count, lCol + 18).End(xlUp).Row
    End If
            Cells(lRow, lCol + 18).Value = Target.Value

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

**rylo** · 02-07-2012, 07:41 PM

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

**LCS** · 02-07-2012, 08:07 PM

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

**rylo** · 02-07-2012, 08:58 PM

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

**LCS** · 02-07-2012, 09:58 PM

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

**rylo** · 02-07-2012, 11:56 PM

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