Solved

VBA - Clear contens of cell, if other cells change

Posted on 2014-02-19
3
2,632 Views
Last Modified: 2014-02-19
Hello Experts,

I am using some VBA code, but it doesn't appear to be working/running? (I have no idea how to verify its even executing)

Here is the code...

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = "pmDimensions" Then
            Range("pmSeal").Value = ""
        End If
    End With
End Sub

Open in new window


pmDimensions consists of 3 cells.   Basically, if any value changes, then I want to clear the contents of pmSeal.

Any ideas what is wrong with the code above?
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
  • 2
3 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39870815
You have to turn off events for the code to behave well:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    With Target
        If .Address = "pmDimensions" Then
            Range("pmSeal").Value = ""
        End If
    End With
    Application.EnableEvents = True
End Sub

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 39870819
Also, the Address property is not the same as the range name.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("pmDimensions")) Is Nothing Then
        Range("pmSeal").Value = ""
    End If
    Application.EnableEvents = True
End Sub

Kevin
0
 
LVL 1

Author Closing Comment

by:Geekamo
ID: 39870855
This worked great - thanks Kevin!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

695 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