• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2794
  • Last Modified:

VBA - Clear contens of cell, if other cells change

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
Geekamo
Asked:
Geekamo
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
GeekamoAuthor Commented:
This worked great - thanks Kevin!
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now