Consulting

Results 1 to 5 of 5

Thread: Current Code works, then when I add a line, Excel Crashes

  1. #1
    VBAX Regular
    Joined
    Oct 2012
    Posts
    35
    Location

    Current Code works, then when I add a line, Excel Crashes

    Hello Experts. I am working on an excel with VBA code that someone else built. It currently works fine. I am trying to enhance it by adding some simple code. As soon as I add one line, and run it, Excel crashes completely. I don't get the debug error or anything, a pure crash, and it has been consistent. Tried restarting Excel and my computer just in case. I have no idea what is causing this. I did notice that this workbook has some code on the Sheet as well as the Model. Here is the code on the sheet, could this be the issue? I have always put all my code into the Module. I noticed it because when i tried to Debug it, it kept bouncing from the module to this code. I am unfamiliar with Case, I am a new VBA user.



    Public Sub Worksheet_Change(ByVal Target As Range)
        
        Select Case Target.Row
            
        Case 4
           Target.Font.ColorIndex = 1
           
        Case 13
        
           'MsgBox (" Worksheet Change")
           
           If ActiveSheet.Cells(6, 1).Value = 1 Then
              Target.Font.ColorIndex = 1
           Else
              Target.Font.ColorIndex = 3
              Target.Interior.ColorIndex = 2
              Target.NumberFormat = "###,##0"
              
           End If
                   
        End Select
        
    End Sub
    Thanks,

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,622
    If there's no message it could be some other code is getting into a never ending loop or there's some code somewhere including the line Apllication.screenupdating=false which gets executed but never gets to its chum, Application.screenupdating=True.
    Which line are you adding?
    What versoin of Excel?
    What are the symptoms of the crash?
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    if your additional code changes cell values, it will fire worksheet change event (as above) for each edited cell
    while the above code does not edit cells only change colours and numberformat, so should not create a loop, it is better to prevent it from running if not required

    application.enableevents = false
    before additional code, set to true after

    if you want further assistance, you would need to show the module code procedure and the code you want to add

  4. #4
    VBAX Regular
    Joined
    Oct 2012
    Posts
    35
    Location
    That Makes sense. I was able to get my code to work by using application.enableevents = false. During testing, another issue came up, I see why the original Developer added this Change Event. When the Cells are changed in Row 13, it should have red text and a white background. When I run through the code, I see it turn red, then white, but after the Sub ends, it defaults back to another format and I cannot figure out where this is happening. Any ideas or key words I can look for that would change the format again after the change event? Thanks again for your help!
    Last edited by snowdyce; 07-16-2014 at 06:23 AM.

  5. #5
    VBAX Regular
    Joined
    Oct 2012
    Posts
    35
    Location
    I found the issue, I have a dynamic formatting sheet that was overriding the format after the change event. My issue is resolved, thank you both for the help.

Tags for this Thread

Posting Permissions

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