change event on specific cell rather than worksheet

F

frendabrenda1

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
 
S

Stefi

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:
 
F

frendabrenda1

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!
 
S

Stefi

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:
 
K

kmwhitt

I realize this is an old post, but I am having the same problem and am not
sure how to handle it. I keep getting an error message that states
"ambiguous name detected". I'm a complete newbie to this whole thing, so
please advise. Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("D6:G549")) Is Nothing Then
With Target
If .Value = "a" Then
..Value = ""
Else
..Value = "a"
..Font.Name = "Marlett"
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$1" Then
Range("C2,C3,C4").Value = ""
End If
End Sub

What variable is it that I am being asked to change?

Thanks,

Kevin
 
D

Dave Peterson

You only get one of these worksheet_selectionchange events per worksheet.

But you can combined the code into one subroutine:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, me.Range("D6:G549")) Is Nothing Then
With Target
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
End If
End With
End If

If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
Me.Range("C2,c3,c4").Value = ""
End If

sub_exit:
Application.EnableEvents = True
End Sub
 
K

kmwhitt

Thanks, Dave! It works.... I find that with this code all I have to do is
click on the field and the other fields get deleted. Is there anyway to have
the delete occur only if I change the data within the drop-down list field?

Thanks again,

Kevin
 
D

Dave Peterson

You could put the code that does the work in the Worksheet_change event.

But I'm not sure which should react that way--the stuff for D6:G549 or the stuff
for C1 (or both)???
 
K

kmwhitt

Thanks, Dave! I got it to work with doubleclick. Now I am having trouble
defining the range. I have 4 cells (not connected) to the others that I want
to include in the range. I have tried:

If Not Intersect(Target, Range("D12:G549", "F5:F8")) Is Nothing Then

How do I define 2 ranges that aren't connected?

Thanks,

Kevin
 
D

Dave Peterson

Try:

Range("D12:G549,F5:F8")


Thanks, Dave! I got it to work with doubleclick. Now I am having trouble
defining the range. I have 4 cells (not connected) to the others that I want
to include in the range. I have tried:

If Not Intersect(Target, Range("D12:G549", "F5:F8")) Is Nothing Then

How do I define 2 ranges that aren't connected?

Thanks,

Kevin
Click to expand...
 
Top