change event on specific cell rather than worksheet

Good morning.

I have an in cell drop down list of vendors that another in cell drop down
list of part numbers relates to. I have it set so that only part numbers
that belong with a chosen vendor appear. The problem that I have is that if
you have already picked a vendor and part number, then change the vendor, the
old part number (which does not belong to the new vendor) still remains. Is
there a way to clear the part number cell on any change to the vendor cell?
(Not on worksheet change as other changes should be allowed without removing
the part number.)
Thanks for your help! brenda

Use worksheet change, but put an IF at the beginning of the sub:

IF Target.Address = vendor-cell then 'use absolute reference, e.g. $A$1
clear part-number-cell
END IF

Regards,
Stefi


„frendabrenda1” ezt �*rta:

> Good morning.
>
> I have an in cell drop down list of vendors that another in cell drop down
> list of part numbers relates to. I have it set so that only part numbers
> that belong with a chosen vendor appear. The problem that I have is that if
> you have already picked a vendor and part number, then change the vendor, the
> old part number (which does not belong to the new vendor) still remains. Is
> there a way to clear the part number cell on any change to the vendor cell?
> (Not on worksheet change as other changes should be allowed without removing
> the part number.)
> Thanks for your help! brenda

I tried
IF Target.address = $E$3 then
Clear $F$3

I got an error for the $ as an invalid character

I also got an error for Clear as a function not defined

I am using excel 2003...does that make a difference?

Thanks again for the help!

"Stefi" wrote:

> Use worksheet change, but put an IF at the beginning of the sub:
>
> IF Target.Address = vendor-cell then 'use absolute reference, e.g. $A$1
> clear part-number-cell
> END IF
>
> Regards,
> Stefi
>
>
> „frendabrenda1” ezt �*rta:
>
> > Good morning.
> >
> > I have an in cell drop down list of vendors that another in cell drop down
> > list of part numbers relates to. I have it set so that only part numbers
> > that belong with a chosen vendor appear. The problem that I have is that if
> > you have already picked a vendor and part number, then change the vendor, the
> > old part number (which does not belong to the new vendor) still remains. Is
> > there a way to clear the part number cell on any change to the vendor cell?
> > (Not on worksheet change as other changes should be allowed without removing
> > the part number.)
> > Thanks for your help! brenda

That was only a frame, the real code is like this:

IF Target.Address = "$E$3" Then
Range("F3").Value = ""
End If


Regards,
Stefi

„frendabrenda1” ezt �*rta:

> I tried
> IF Target.address = $E$3 then
> Clear $F$3
>
> I got an error for the $ as an invalid character
>
> I also got an error for Clear as a function not defined
>
> I am using excel 2003...does that make a difference?
>
> Thanks again for the help!
>
> "Stefi" wrote:
>
> > Use worksheet change, but put an IF at the beginning of the sub:
> >
> > IF Target.Address = vendor-cell then 'use absolute reference, e.g. $A$1
> > clear part-number-cell
> > END IF
> >
> > Regards,
> > Stefi
> >
> >
> > „frendabrenda1” ezt �*rta:
> >
> > > Good morning.
> > >
> > > I have an in cell drop down list of vendors that another in cell drop down
> > > list of part numbers relates to. I have it set so that only part numbers
> > > that belong with a chosen vendor appear. The problem that I have is that if
> > > you have already picked a vendor and part number, then change the vendor, the
> > > old part number (which does not belong to the new vendor) still remains. Is
> > > there a way to clear the part number cell on any change to the vendor cell?
> > > (Not on worksheet change as other changes should be allowed without removing
> > > the part number.)
> > > Thanks for your help! brenda