Results 1 to 4 of 4

Need to add if error code to worksheet change event to stop a run-time error

Threaded View

KT99 Need to add if error code to... 11-18-2014, 10:09 AM
stnkynts Re: Need to add if error code... 11-18-2014, 12:04 PM
KT99 Re: Need to add if error code... 11-18-2014, 01:02 PM
stnkynts Re: Need to add if error code... 11-18-2014, 03:10 PM
  1. #1
    Registered User
    Join Date
    09-19-2014
    Location
    Philadelphia,PA
    MS-Off Ver
    2010
    Posts
    54

    Need to add if error code to worksheet change event to stop a run-time error

    Hi all,

    I have a code that produces a mssg box if any "P" value is selected (data validation) in column A. The mssg box allows the user to specify how many additional lines they need to add, and adds them by copying the line x number of times. However, I often encounter a run-time error that completely freezes excel and forces me to quit excel via task manager. Is there and if error code I could tie around this?

    Any help would be greatly appreciated.


    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range, nRows As Long
        'Application.EnableEvents = False
        
    'Sheets("Step2").Unprotect Password:="**********"
    
    
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        'Application.EnableEvents = False
        'On Error GoTo Whoa
        For Each cell In Intersect(Target, Range("A:A"))
           
            If cell.Value = "P" Then
                Application.EnableEvents = False
                Sheets("Step2").Unprotect Password:="**********"
                nRows = Application.InputBox("How many circuits are NOT-EXEMPT from this account?", "Insert Rows", , , , , , 1)
                
                If nRows = 1 Then
                cell.Value = "-"
                Application.CutCopyMode = False
                Application.EnableEvents = True
                
                ElseIf nRows > 1 Then
                cell.Value = "-"
                cell.EntireRow.Copy
                cell.Offset(1).Resize(nRows - 1).EntireRow.Insert xlShiftDown
                Application.CutCopyMode = False
                Application.EnableEvents = True
                End If
            End If
        Next cell
        
    
    Sheets("Step2").Protect Password:="********"
    Sheets("Step2").EnableSelection = xlUnlockedCells
    
            
    'Letscontinue:
        'Application.EnableEvents = True
        'Exit Sub
    'Whoa:
        'MsgBox Err.Description
        'Resume Letscontinue
            
            
        'Application.CutCopyMode = False
        'Application.EnableEvents = True
    End If
        'Application.EnableEvents = True
    End Sub
    As you can see, I tried implementing my own if error code but it did not work. I still ran into the problem of excel shutting down.
    Last edited by KT99; 11-18-2014 at 10:12 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Stop Worksheet change event from interfering with macro code
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2013, 09:11 PM
  2. Run Time error due to event change code when I delete columns in file
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2012, 12:53 PM
  3. runtime error, isempty, worksheet change event
    By Code Flunkie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-28-2011, 05:17 PM
  4. Stop code from triggering worksheet event
    By mashley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2010, 11:47 AM
  5. Replies: 5
    Last Post: 06-23-2005, 06:05 PM

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