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

Never mind I solved it :)
Thx for your help today

Calle

I have a a problem with the script. It works great on my computer with excel
2003 but when I run it on a computer with excel 2002 I get error message that
it couold not compile due to not beeing able to find "project or library".

Calle

"Calle" wrote:

Never mind I solved it :)
Thx for your help today

Calle

On the problem pc:

Open Excel
go to to the VBE
select your project
click on Tools|references
See if there's something marked missing.

Uncheck that option.

In fact, you may be better off doing this on the pc that "owns" that workbook.
Then save it without that missing reference and distribute the workbook once
more.

Calle wrote:

I have a a problem with the script. It works great on my computer with excel
2003 but when I run it on a computer with excel 2002 I get error message that
it couold not compile due to not beeing able to find "project or library".

Calle

"Calle" wrote:

Never mind I solved it :)
Thx for your help today

Calle

--

Dave Peterson