+ Reply to Thread
Results 1 to 6 of 6

Macro not responding to cell deletion

Hybrid View

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

    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. 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-14-2008 at 02:08 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    I don't think that deleting a cell's value will trigger the event. maybe if you try the Calculate event
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    So do you suggest taking out the following part of the worksheet_change event and doing this instead?

    Private Sub Worksheet_Calculate(ByVal Target As Range)
    Dim Rng2 As Range
    Set Rng2 = Range("J17:J116")
    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
    End If
    End Sub
    Last edited by jman0707; 11-12-2008 at 02:05 PM.

  4. #4
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Yeah it's not working for me. I don't know the proper syntax with this type of event. Here's what it's telling me.

    Compile Error: Prodecure declaration does not match description of event or procedure having the same name.

    Any ideas?
    Last edited by jman0707; 11-12-2008 at 02:10 PM.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Attach an example workbook

  6. #6
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Here you go! Column K is not responding when column J gets deleted. It should trigger, since it's the worksheet_change event....
    Attached Files Attached Files
    Last edited by jman0707; 11-13-2008 at 12:18 PM.

+ 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