+ Reply to Thread
Results 1 to 11 of 11

Worksheet stopped working.

  1. #1
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    108

    Worksheet stopped working.

    I need help trying to find out why this worksheet stopped working without any changes made intentionally . I have run the debug and checked the code but I can't seem to find what is stopping the program from running. The code is too long to list here, the system won't let me show it here. The code is in the workbook on the first page ("Sign_In"). All help is greatly appreciated!!!!
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,472

    Re: Worksheet stopped working.

    How has it stopped working?
    If posting code please use code tags, see here.

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2007
    Posts
    5,551

    Re: Worksheet stopped working.

    Hello COURTTROOPER,

    Am I correct in assuming that your posted Workbook is linked to another Workbook with a sheet Named "CAP_PD_Deposit", as your Code refers to same.

    Does the attached now work for you?

    Regards.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2007
    Posts
    5,551

    Re: Worksheet stopped working.

    @ Norie,

    The Code boasted an End If without Block If.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    27,228

    Re: Worksheet stopped working.

    Use Debug | Compile VBAProject to highlight errors in structure and syntax.As Winin says, you have an extra "End If"
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    108

    Re: Worksheet stopped working.

    Norie,

    The worksheet stopped entering the items listed in each If Then for the Target.Value input by user (ie ATR, AV, CO, etc.). I did run the debug several times without errors showing up. That's why I am asking for the help. I will run it again though when I get home.

    Winon,
    The CAP_PD_Deposit is another worksheet I did not include because it does nothing during this process.

  7. #7
    Valued Forum Contributor
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    820

    Re: Worksheet stopped working.

    1. Same as above, something is wrong with 'End If' and the same with the logical structure.
    A. Why in segment:
    Please Login or Register  to view this content.
    'End If' is blocked ?

    B. It seems that there are two 'End If' for the 'AV' segment.
    C. Two 'End If' at the end are free/single.

    2. Why below:
    Please Login or Register  to view this content.
    are not 'Const' ?
    These "values" surely/certainly do not change.

    3. Why below:
    Please Login or Register  to view this content.
    is in the middle (before 'Select Case Target.Column') and at the beginning is blocked ?

    4. Just in case, run (in Immediate window) 'Application.EnableEvents = True' property before running the macro.

  8. #8
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    108

    Re: Worksheet stopped working.

    Thanks to all,
    Norie, Winon, porucha vevrku, TMS
    I am new to VBA as you can tell from my code and post. You all got me headed in the right direction although I still am not getting the "End If" error reporting but I have separated my combination of code (multiple If Then's) and have most of the code working. I am still working on separating the If Then's. I was trying to shorten up and speed up my code by combining them which seems to have been my problem. The double Dim statements I tried because I could not get code working and I though maybe the Const was not flowing through to that sheet. Could some one explain how to do the suggestion of: 4. Just in case, run (in Immediate window) 'Application.EnableEvents = True' property before running the macro, by porucha vevrku. Also the rem outs were to see if I could get things going.
    The
    Please Login or Register  to view this content.
    was from some of the help I had received from other people that I did not know if that was causing the problem of not being able to find errors in the debug. I have had several helpers that included the error checking that I did not understand and was afraid to remove it.
    I will post a copy of my completed sheet after I get it all working. I did try to download the workbook from Winon but the "red sheet1" link would not work. Thanks again for all the help and please check back on me in a few days as I hope to finish.
    Thanks for all the help!! To be continued...

  9. #9
    Valued Forum Contributor
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    820

    Re: Worksheet stopped working.

    Take a look at this:

    1. Attached image, there is an error

    2. A bit ordered code (notches, arrangement of commands), each 'If ... End If' has its own position

    Was this your intention for the 'If ... End If' layout ?

    (see attached txt - the code is too long for displaying in this thread)

    3. In the macro you have 'Application.EnableEvents = False', so you did something with it, so it's better to give 'Application.EnableEvents = True' "at the beginning of the macro tracking" and only then to run the macro

    4. 'If Target.Cells.Count> 1 Then Exit Sub' is given (usually) at the beginning of the macro ... this is rather a "global directive" that affects the entire command sequence in the macro (prevents the macro from being run, when selecting multiple cells)
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by porucha vevrku; 12-28-2017 at 01:55 PM.

  10. #10
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    108

    Re: Worksheet stopped working.

    Sorry it took me so long to get back on, this but I had issues that took me away from the net.
    I have looked at your text file and the answers to why all the End If of Missing End If is that I have been using parts of others code and don't really understand all they have included. I use the debug and if it tells me I need an end if then I put it in. I am trying to learn this out of some VBA books I have found. I have gotten my code to run with the help you all have given me. I have tried to follow what I see in code structure for the staggered If and End If and didn't know there was a better structure to follow. I do get lost if all the If's sometimes.

    I thank you all for all your help!!

  11. #11
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2007
    Posts
    5,551

    Re: Worksheet stopped working.

    Hello COURTTROOPER,

    You are welcome. Glad we could help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Click on the star icon in the lower left part of any of the contributor's post and add Reputation.

    Thanks.

+ 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