Solved

VBA - ambiguous Name?

Posted on 2014-02-19
6
435 Views
Last Modified: 2014-03-24
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?
0
Comment
Question by:Geekamo
6 Comments
 
LVL 53

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 39871449
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
 
LVL 1

Author Comment

by:Geekamo
ID: 39871455
@ 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
 
LVL 53

Expert Comment

by:Bill Prew
ID: 39871460
Sorry, had to edit my first post, take a look now.

~bp
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39882233
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39949777
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question