I have a drop down list using the validate list option.
When I select a product from the dro down list it excel display my choices
in a cell using a simple VBA script. But there is one product in the list
that I don't want to be displayed in that cell. How do I make an exeption for
that cell?

This is the script I am using now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$16" Then
If Target < "" Then
Cells(17, 7) = Cells(17, 7) & "" & Target
Selection.Offset(0, 0).ClearContents


End If
End If
End Sub

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$16" Then
If LCase(Target.Value) = "whatyoudon'twanthere" Then
'do nothing
Else
If Target.Value < "" Then
'stop the change from firing this event
Application.EnableEvents = False
Cells(17, 7) = Cells(17, 7) & "" & Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub




Calle wrote:

I have a drop down list using the validate list option.
When I select a product from the dro down list it excel display my choices
in a cell using a simple VBA script. But there is one product in the list
that I don't want to be displayed in that cell. How do I make an exeption for
that cell?

This is the script I am using now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$16" Then
If Target < "" Then
Cells(17, 7) = Cells(17, 7) & "" & Target
Selection.Offset(0, 0).ClearContents

End If
End If
End Sub

--

Dave Peterson

Hi!
It works great. But it doesnt work with Capital letters in the product
name... How come?

Carl-Johan

"Dave Peterson" skrev:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$16" Then
If LCase(Target.Value) = "whatyoudon'twanthere" Then
'do nothing
Else
If Target.Value < "" Then
'stop the change from firing this event
Application.EnableEvents = False
Cells(17, 7) = Cells(17, 7) & "" & Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub




Calle wrote:

I have a drop down list using the validate list option.
When I select a product from the dro down list it excel display my choices
in a cell using a simple VBA script. But there is one product in the list
that I don't want to be displayed in that cell. How do I make an exeption for
that cell?

This is the script I am using now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$16" Then
If Target < "" Then
Cells(17, 7) = Cells(17, 7) & "" & Target
Selection.Offset(0, 0).ClearContents

End If
End If
End Sub

--

Dave Peterson

also I can't seem to use this script on 2 different drop down lists. I just
copied it and changed the cells but it won't work. the 2 drop down lists are
in the same worksheeet....

First, I would bet that since I compare lcase() with the value you didn't want,
I bet you used upper/lower case when you typed in that line.

Second, you get one of these events per worksheet. You have to include/merge
both pieces of code within that same procedure.

I don't know what other cell contains that second dropdown list or where to
build the string, but this could get you started:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range

Set myRng = Me.Range("I16,X99")

With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, myRng) Is Nothing Then Exit Sub
If .Value = "" Then Exit Sub

Select Case LCase(.Address(0, 0))
Case Is = "i16"
If LCase(.Value) = LCase("what you don't want") Then
'skipit
Else
Application.EnableEvents = False
Me.Range("G17").Value = Me.Range("G17").Value & .Value
.ClearContents
Application.EnableEvents = True
End If
Case Is = "x99"
If LCase(.Value) = LCase("what you don't want") Then
'skipit
Else
Application.EnableEvents = False
Me.Range("x1000").Value = Me.Cells("x1000").Value & .Value
.ClearContents
Application.EnableEvents = True
End If
End Select
End With
End Sub

====
And you'll have to watch your typing.

I'm using this line:
Select Case LCase(.Address(0, 0))

That means that I have to use lower case in the comparison -- in this line:
Case Is = "i16"

Calle wrote:

also I can't seem to use this script on 2 different drop down lists. I just
copied it and changed the cells but it won't work. the 2 drop down lists are
in the same worksheeet....

--

Dave Peterson

Dave, you are great! Thanks. I really appricate this. One more "small"
question... Can I put a "clear" option in the list. Like a line that says
"clear selections" witch has the function of cleaning the cells ( G17, and
G20 in my case) where the resluts are presented.

I am using a validation/list so the source of the products is in another
woorksheet