+ Reply to Thread
Results 1 to 1 of 1

Worksheet_Change macro not responding to cell deletion

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184

    Worksheet_Change macro not responding to cell deletion

    I have a code (thanks royUK and Leath!) where, if column E is "Annuity", you can type a value in column J and it runs a formula to calculate a value for column K. If E is not "Annuity" it clears the contents of J (which should clear K). What's not working is when column J gets cleared or manually deleted, column K must be blank. The worksheet_change event is not working for deletion of column J.

    It also isn't reprotecting the sheet after I delete a value in column J. I've tried pasting the .Protect argument in a bunch of different places and can't get it to work. I need it to reprotect no matter what is typed in or deleted. Here's the code, and I've attached the workbook as well. Thanks!

    Private Sub Worksheet_Change(ByVal Target As Range)
    Const pw As String = "blondie"
    Dim Rng As Range, c As Range, Rng2 As Range, Rng3 As Range, i As Long, lastrow As Long
    Set Rng = Range("E17:E116")
    Set Rng2 = Range("J17:J116")
    Set Rng3 = Range("K17:K116")
    With Sheets("NML Inventory")
    .Unprotect pw
    If Not Intersect(Target, Rng2) Is Nothing Then
        lastrow = Range("J65536").End(xlUp).Row
            For i = 17 To lastrow
            If .Cells(i, 10) = 0 Or IsEmpty(.Cells(i, 10)) Or .Cells(i, 5) <> "Annuity" Then
            Cells(i, 11).ClearContents
            Else: Cells(i, 11) = Sheets("INPUT").Cells(28, 13) * Sheets("NML Inventory").Cells(i, 10)
            End If
        Next
    Else
        If Not Intersect(Target, Rng) Is Nothing Then
            For Each c In Rng
                Select Case c.Value
                    Case "Annuity"
                        c.Offset(0, 3).ClearContents
                        c.Offset(0, 4).ClearContents
                    Case "DI", "LTC"
                        c.Offset(0, 3).ClearContents
                        c.Offset(0, 5).ClearContents
                    Case Else
                        c.Offset(0, 5).ClearContents
                End Select
            Next c
            .Protect pw, AllowSorting:=True, AllowFiltering:=True
        End If
    End If
    End With
    End Sub
    Last edited by jman0707; 11-13-2008 at 11:59 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1