Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Sep 2005
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I get the following error when i click my save button:
    Run-time error 1004 - Application-defined or object-defined error.

    Then when i debug it highlights in the code below (what is underlined and bold):

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Columns(6)) Is Nothing Then
    If Application.WorksheetFunction.CountIf(Target.Offse t(0, -5).Resize(1, 6), Target) <> 6 Then Application.EnableEvents = False
    With Target.Offset(0, 1)
    .Value = "No"
    .Font.Color = vbRed
    .Font.Bold = True
    .Font.Underline = True
    Application.EnableEvents = True
    End With
    Else
    Application.EnableEvents = False
    With Target.Offset(0, 1)
    .Value = "Yes"
    .Font.Color = vbGreen
    .Font.Bold = True
    Application.EnableEvents = True
    End With

    End If
    Target.Offset(1, -5).Select
    End If

    End Sub


    I am not sure what to do with this - any help appreciated.
    Thanks
    [attachment=88779:CHECK UNIT NUMBERS.xlsm]
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The error occurs when the DeleteRowsWB macro is run. When the macro deletes the rows, the change event macro fires. Wrap the DeleteRowsWB in:
    Application.EnableEvents=False and Application.EnableEvents=True

    The macro will become:
    Code:
    Application.EnableEvents = False
    	
    	Dim ws As Worksheet, lRow As Long
    	Set ws = Worksheets("WB")
    	lRow = ws.Range("A" & Rows.count).End(xlUp).Row
    	ws.Rows("2:" & lRow).Delete Shift:=xlUp
    	Set ws = Nothing
    Application.EnableEvents = True
    End Sub
    For use with Excel 2007's larger sheet size, I changed this line:
    lRow = ws.Range("A65536").End(xlUp).Row
    to
    lRow = ws.Range("A" & Rows.Count).End(xlUp).Row

  3. #3
    Lounger
    Join Date
    Sep 2005
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you!!!!

Posting Permissions

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