Condiditional Formatting - Out Of Stack Space - In Change Event Code

    Can anyone help me please the code i have got is attached below but the problem i am having is when i enter the letter "H" which is calculated in AJ and i enter it fast it comes up with the error "Out Of Stack Space error 28"


    AJ in the code below is where the cumlative value is stored, and AI is the value that it is measured against it AJ is greater than AI the message is displayed


    I am in need of urgent help and would appreciate some help, the project is for a charity and i have promised them they will have it before the end of the year, this is at no cost to them


    Re: Out Of Stack Space (error 28)


    Well first of all you're changing the worksheet without telling excel NOT to catch events.


    What you need to try first is adding:


    Code
    Application.EnableEvents=False


    at the start of the code...
    This will make sure that the sheets

    Code
    Worksheet_Change


    event isn't triggered when you clear contents or add contents to cells.
    I should think that this will clear your error message!

    Re: Out Of Stack Space (error 28)


    Change the beginning of the code


    Code
    With Application 
    .CellDragAndDrop = False 
    .CutCopyMode = False
End With


    To:

    Code
    With Application 
    .CellDragAndDrop = False 
    .CutCopyMode = False
    .EnableEvents = False
End With


    :)

    Re: Out Of Stack Space (error 28)


    Zerothi


    Thanks for that it has stoped the erorr message but if i enter the letter h fast it allows the entry to still happen


    what would this be

    Re: Out Of Stack Space (error 28)


    What do you mean by writing it "fast"?


    And what entry still happens?


    From the code it seems that if you enter "h" in a cell it will capitalize it and then make the interior and the font color change, isn't that what you want?

    Re: Out Of Stack Space (error 28)


    Zerothi


    when i enter the letter h fast as h enter h enter h enter h enter h enter it converts to uppercase and turns the H to green but it has allowed entry in cells where it should not as this is greater than AI


    Thanks John

    Re: Out Of Stack Space (error 28)


    Please give as much information as possible, it's hard to see the sheet :D


    What is the value in AI? i don't know if it IS larger then AJ.


    If the value in AI is a number then a letter will always be "Bigger" than the number... And then no matter what it will trigger...

    Re: Out Of Stack Space (error 28)


    Zerothi


    =COUNTIF($D14:$AH14,"H") This converts the H to a Number and this is in AJ,
    The Value in AI is the Amount of Holiday they are entitled to if AJ becomes greater than AI the Message is displayed
    If i enter the h fast it overides the Message ie
    if i enter the H enter fast five or six times the message comes on the six h, but if i enter
    ie h enter and dont enter another h for say 2 seconds the message is displayed and does not allow entry


    Thanks Jch

    Re: Out Of Stack Space (error 28)


    Ok... That could make sense... If you enter the "H" fast enough you wouldn't reach the end of the code...


    I would try to add

    Code
    ScreenUpdating = False


    Like this:


    Code
    With Application 
    .CellDragAndDrop = False 
    .CutCopyMode = False 
    .EnableEvents = False 
    .ScreenUpdating = False
End With


    This should prevent the user from interacting with the sheet for as long as the code is executed.


    Just of curriosity... The "H" enter you fire quickly... Are they all in the same row?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login