|
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 |
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 |
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 |
Help!!!
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 "Dave Peterson" wrote: 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 |
Help!!!
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)??? kmwhitt wrote: 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
Help!!!
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 "Dave Peterson" wrote: 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)??? kmwhitt wrote: 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
Help!!!
Try:
Range("D12:G549,F5:F8") kmwhitt wrote: 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 "Dave Peterson" wrote: 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)??? kmwhitt wrote: 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 "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Help!!!
Worked like a charm!
Thanks, Kevin "Dave Peterson" wrote: Try: Range("D12:G549,F5:F8") kmwhitt wrote: 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 "Dave Peterson" wrote: 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)??? kmwhitt wrote: 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 "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 10:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com