| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help!!!
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 "Stefi" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help!!!
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 kmwhitt wrote: 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 "Stefi" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I change a cell and cause an automatic change in other cells | Excel Worksheet Functions | |||
| copy combobox - cell link to change automatically | Excel Worksheet Functions | |||
| Change cell color dependin on date ... | Excel Worksheet Functions | |||
| How do I change color of active cell in Excel | Excel Discussion (Misc queries) | |||
| Worksheet Row Change event | Excel Discussion (Misc queries) | |||
Hybrid Mode
