Page 1 of 2 12 LastLast
Results 1 to 10 of 13

VBA Help deleting contents of a cell on worksheet change event

This is a discussion on VBA Help deleting contents of a cell on worksheet change event within the Excel Questions forums, part of the Question Forums category; Any help much appreciated. I have a spreadsheet and a worksheet change event which checks Column B and show's a ...

  1. #1
    New Member
    Join Date
    Nov 2010
    Posts
    24

    Default VBA Help deleting contents of a cell on worksheet change event

    Any help much appreciated. I have a spreadsheet and a worksheet change event which checks Column B and show's a message box when either "Personal" or "Corporate" is entered in the cell. It uses an OldValue variable to ensure the macro doesnt fire when the change is occurring to a currently blank cell.

    What I also need the macro to do is to also delete the contents of columns D, E and F in the row corresponding to the cell whenever "Personal" or "Corporate" is entered in the cell. Code below.

    Public OldValue As String
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("B2:BF" & Rows.Count)) Is Nothing Then Exit Sub

    With Application
    On Error GoTo ErrHandler
    .EnableEvents = False
    .Undo
    OldValue = Target.Cells(1).Value
    .Undo
    .EnableEvents = True
    ActiveCell.Offset(0, 1).Select

    End With

    With Target
    If OldValue = "Personal" Then MsgBox "Please note products available are specific to either Personal or Corporate applications."
    If OldValue = "Corporate" Then MsgBox "Please note products available are specific to either Personal or Corporate applications."

    End With

    ErrHandler:
    Application.EnableEvents = True
    If Err.Number <> 0 Then
    MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
    End If
    End Sub

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    Quote Originally Posted by ASanders View Post
    I have a spreadsheet and a worksheet change event which checks Column B and show's a message box when either "Personal" or "Corporate" is entered in the cell.
    If you only care about monitoring the change in column B then why do you have this statement stretching out to column BF?

    If Intersect(Target, Range("B2:BF" & Rows.Count)) Is Nothing Then Exit Sub

    If you only care about entries in column B, substitute that line for this:

    If target.Column <> 2 then exit Sub

  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    24

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    Thanks Tom. Was more interested in the deletion of contents from corresponding cells in Columns D, E and F?

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    My point is, the Change event won't be triggered if you only limit it to column 2 (column B), meaning you can delete or do anything you want in the other columns such as D, E, and F.

    If you must keep that Range Intersect statement in there as it is, then immediately after it stick in this line and see if it achieves what you want:

    If IsEmpty(Target) Then Exit Sub

  5. #5
    New Member
    Join Date
    Nov 2010
    Posts
    24

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    Thanks Tom. Thats fine. I'll make that change to specify the range to Column 2. What I want to know is how I can code in the system to automatically delete the contents of the corresponding cells in D, E and F once the user changes the contents of Column 2 from 'Personal' to 'Corporate' or vice versa. I have it so a message box appears for each of these scenarios. I just need to add the delete part. Hope that makes more sense.

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    Just to be clear, do you want this event to trigger if...

    ...Personal or Corporate is entered as a new item
    or
    ...Personal or Corporate was originally in the cell and just got replaced by some other item
    or
    ...either Personal or Corporate was originally in the cell, and/or Personal or Corporate was just entered.

    Also, is the message box the same for both Personal and Corporate.

  7. #7
    New Member
    Join Date
    Nov 2010
    Posts
    24

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    There's a list validation in Column 2 so that only Personal or Corporate can be selected by the user. The original value is blank. If the user enters in either Personal or Corporate for the first time, I do not want the event to trigger at all. Only after that initial entry/selection of Personal or Corporate do I want the event to trigger on change. Thanks.

  8. #8
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    OK then, the first thing you should do is delete the entire change event as you have it, including deleting this too:

    Public OldValue As String



    Next, paste this into your worksheet module and see if it does what you want:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2:BF" & Rows.Count)) Is Nothing Then Exit Sub
    With Target
    If .Column <> 2 Or .Cells.Count > 1 Then Exit Sub
    If IsEmpty(Target) Then Exit Sub
     
    Dim OldVal$, NewVal$
    NewVal = .Value
     
    With Application
    .EnableEvents = False
    .Undo
    OldVal = Target.Value
    Target.Value = NewVal
    .EnableEvents = True
    End With
     
    .Offset(0, 1).Activate
     
    Select Case OldVal
    Case "Personal", "Corporate"
    MsgBox "Please note products available are specific to either Personal or Corporate applications.", , "FYI"
    Range(Cells(.Row, 4), Cells(.Row, 6)).ClearContents
    End Select
     
    End With
    End Sub

  9. #9
    New Member
    Join Date
    Nov 2010
    Posts
    24

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    That works perfect! Can I ask some questions on the code and what it does:

    1. "With Target If .Column <> 2 Or .Cells.Count > 1 Then Exit Sub" - what is the cells.count> 1 asking the system to evaluate?
    2. If IsEmpty(Target) Then Exit Sub - is that just telling the system to not fire the event if the cell's empty to begin with?
    Thanks.

  10. #10
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: VBA Help deleting contents of a cell on worksheet change event

    Quote Originally Posted by ASanders View Post

    1. "With Target If .Column <> 2 Or .Cells.Count > 1 Then Exit Sub" - what is the cells.count> 1 asking the system to evaluate?
    Translated:
    If the cell being changed is not in column B then exit the Change event.
    and
    Even if the change being made is happening in column B, if more than one cell is attempted to be changed at the same time, then exit the change event.



    Quote Originally Posted by ASanders View Post


    2. If IsEmpty(Target) Then Exit Sub - is that just telling the system to not fire the event if the cell's empty to begin with?

    No. It is VBA telling itself to do nothing if the Delete key is pressed, or if the cell is cleared of contents. The "if the cell's empty to begin with" is handled by the Select Case structure that evaluates the presence, or lack thereof, of "Personal", or "Corporate" having been the changed cell's contents before the change was made. If the cell was empty to begin with, that automatically qualifies as a resulting False evaluation and hence no action takes place to clear the cells in D:F.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com