Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA - ambiguous Name?

Posted on 2014-02-19
6
Medium Priority
?
452 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 57

Accepted Solution

by:
Bill Prew earned 2000 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 57

Expert Comment

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

~bp
0
 
LVL 46

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 49

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

688 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