Geekamo
asked on
VBA - ambiguous Name?
Hello Experts,
I am trying to use these two VBA codes... But it's giving me an error saying it's an ambiguous name.
I don't know enough about the code to fix it.
As you can see, I have the ranges/namecell that I want the code to look at - if that cell changes ,then clear the contents of the other cells.
The first section of code does work - and I basically copied it again, but in more named refs - figuring I could just reuse the code but clearly they don't work together.
Any ideas?
I am trying to use these two VBA codes... But it's giving me an error saying it's an ambiguous name.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
If Not Intersect(Target, Range("pmDimensions")) Is Nothing Then
Range("pmSeal").Value = ""
End If
End With
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
If Not Intersect(Target, Range("pmObject")) Is Nothing Then
Range("pmDimensions,pmSeal,pmBI").Value = ""
End If
End With
Application.EnableEvents = True
End Sub
I don't know enough about the code to fix it.
As you can see, I have the ranges/namecell that I want the code to look at - if that cell changes ,then clear the contents of the other cells.
The first section of code does work - and I basically copied it again, but in more named refs - figuring I could just reuse the code but clearly they don't work together.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, had to edit my first post, take a look now.
~bp
~bp
Here's another way to write this:
If this seems cluttered, we can refactor it with a new ClearRange routine, like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case False
Case Intersect(Target, Range("pmDimensions")) Is Nothing
Application.EnableEvents = False
Range("pmSeal").ClearContents
Application.EnableEvents = True
Case Intersect(Target, Range("pmObject")) Is Nothing
Application.EnableEvents = False
Range("pmDimensions,pmSeal,pmBI").ClearContents
Application.EnableEvents = True
End Select
End Sub
If this seems cluttered, we can refactor it with a new ClearRange routine, like this:
Public Sub ClearRange(parmRange As Range, Optional parmQuiet As Boolean = True)
If parmQuiet Then
Application.EnableEvents = False
parmRange.ClearContents
Application.EnableEvents = True
Else
parmRange.ClearContents
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case False
Case Intersect(Target, Range("pmDimensions")) Is Nothing
ClearRange Range("pmSeal")
Case Intersect(Target, Range("pmObject")) Is Nothing
ClearRange Range("pmDimensions,pmSeal,pmBI")
End Select
End Sub
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
I assume we're talking about this line...
Open in new window
I'm not sure what part of that line I need to change?