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.

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

Open in new window


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?
LVL 1
GeekamoAsked:
Who is Participating?
 
Bill PrewCommented:
You have two subroutines with the same name, that isn't allowed.  You will want to add all the logic to the single subroutine, like:

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
        If Not Intersect(Target, Range("pmObject")) Is Nothing Then
            Range("pmDimensions,pmSeal,pmBI").Value = ""
        End If
    End With
    Application.EnableEvents = True
End Sub

Open in new window

~bp
0
 
GeekamoAuthor Commented:
@ bp

I assume we're talking about this line...

Private Sub Worksheet_Change(ByVal Target As Range)

Open in new window


I'm not sure what part of that line I need to change?
0
 
Bill PrewCommented:
Sorry, had to edit my first post, take a look now.

~bp
0
 
aikimarkCommented:
Here's another way to write 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

Open in new window


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

Open in new window

0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.