Drop Down List Help

[Guest]

I am creating a drop down list however I want to allow the user to select
multiple items from the list.

Is this possible and if so how?

Thanks,
Jodie

 

[Guest]

Try Debra Dalgleish's sample file at:
http://www.contextures.com/excelfiles.html

Under Data Validation,
look for: DV0017 - Select Multiple Items from Dropdown List

 

[Guest]

Thank you for the reply however what exactly do I do with the code that she
tells the read to view?

I am a beginner and have no idea how to incorporate this code to the 2
columns of data that I would like to apply this to.

Thanks,
Jodie

 

[Max]

Which example sheet in Debra's sample (she shows several example) best suits
what you're after?

Post in plain text here, more specifics about your sheet set-up, some sample
data and expected results

 

[Guest]

The "samecell" worksheet is what I am trying to accomplish.

I have 2 columns of data that I would like to have the user select as Debra
shows in the "samcell" worksheet.

The user should be able to select any where from 1 to all items on the drop
down list for each column.

The columns I will need to have this code written for are column G titled
"ROOT CAUSE" and column H titled "ACTION".

Here is a sample of data from my worksheet using columns A thru I:

DPT CLASS SKU SKU DESCRIPTION UNIT FILL RATE SEGMENT ROOT CAUSE ACTION GET
WELL
29 9 174319 TP Holder 90% Seasonal Residual Push No action to take - all
IDCs empty NA

Thanks,
Jodie

 

[Max]

Try this in a copy of your book ..

Right-click on the Excel icon to the left of File on the main menu > Choose
View Code

Copy n paste the workbook open code below (1) into the whitespace on the
right

'------ (1) -----
Option Explicit

Private Sub Workbook_Open()
ThisWorkbook.Sheets("SameCell") _
.Protect DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
End Sub
'----------------

Press Alt+Q to get back to Excel

Right-click on the sheet (where you have the DV in col G. Col G = col "7")

choose View code

Copy n paste the sheet code below (2) into the whitespace on the right

'-------(2)------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 7 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
'----------

Press Alt+Q to get back to Excel

Save the file, close it and re-open

Test out the DV you have in col G on this sheet. It should function as
desired.

I'm not savvy enough to amend Debra's code to make it work for col H as well
on the same sheet. Hang around awhile for insights from others versed in vba
to jump in.

---

 

[Guest]

Thank you so much Max!!!!!!

I was able to do the same for column 8 by entering "or 8" on the same line
as 7.

You were a tremendous help!!

Jodie

 

[Max]

Welcome, Jodie. Gratified to know that.

Thanks for telling me how you modified it to work for col H

 

[Guest]

One last question....a colleague asked is there any way to only show certain
items in column 8 when a specific item is selected in column 7?

For instance if the user selected "Residual" in column 7 we only want "Push
to clear IDCs" and "No action to take - all IDCs empty" to be available for
selection in the drop down list in column 8.

Is this possible?

Thanks,
Jodie

 

[Max]

Debra covers this "dependent list" aspect at her:
http://www.contextures.com/xlDataVal02.html
Excel -- Data Validation -- Create Dependent Lists

It's beyond me though, how to integrate this new aspect to work seamlessly
with the "samecell" code that you're using. Hang around awhile. Perhaps
Debra herself, or others versed in vba might drop by here to lend you some
thoughts. If nobody drops by, suggest you put in a new posting in
..programming.