+ Reply to Thread
Results 1 to 11 of 11

Running macro on worksheet change is crashing workbook

  1. #1
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Running macro on worksheet change is crashing workbook

    Hi everyone,

    I have the following code in a worksheet:

    Please Login or Register  to view this content.
    The value in cell B15 changes based in another macro that runs in a loop. When the first value is put into B15 the proper value in B18 populates just fine, but then the entire workbook hangs and requires a restart of excel.

    Any suggestions how I can update the value in cell B18 using the same or similar lookup method whenever cell B15 is updated?

    Thanks!!
    Last edited by 6StringJazzer; 09-23-2016 at 12:09 PM. Reason: closed code tag

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Running macro on worksheet change is crashing workbook

    Hi,

    When Excel hangs up like that, there is often an endless loop caused by some Excel Event. Disabling Excel Events often corrects the problem. Try something like the following code, which is activated only when 'B15' changes value.

    Please Login or Register  to view this content.
    Lewis

  3. #3
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Running macro on worksheet change is crashing workbook

    Your code is changing the value of B18 and so the change event starting again and it's doing so again and again. To get rid of this line at the beginning of your code (After Private Sub Worksheet_Change(ByVal Target As Range) :
    Please Login or Register  to view this content.
    and this line at the end of your code (Before End Sub):
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Running macro on worksheet change is crashing workbook

    Hi LJ and Sanram,

    Thanks very much, and especially to LJ for the updated code. When I run the code on individual entries in B15 it works fine, however when I run the normal macro which goes through the list, B18 will show FALSE instead of the proper result. Here's the actual macro which runs through the list:

    Please Login or Register  to view this content.
    Its the SPATH bit that's supposed to show the actual result from the Worksheet_Change code, but instead its showing FALSE. Any ideas?

    Thanks very much for your help!!

  5. #5
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Running macro on worksheet change is crashing workbook

    Hi,

    Please Login or Register  to view this content.
    The above code does not designate which sheet is used, and the 'Active Sheet' may be a sheet other than what you expect. At this point, further troubleshooting probably requires an uploaded sample workbook.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Lewis

  6. #6
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Running macro on worksheet change is crashing workbook

    Hi LJ,

    I'm afraid a sample workbook wouldn't be representative if what I'm working with.

    I ran a msgbox on the Filename, and it came up blank. But when I run a msgbox on the SPath and SNames individually, I get the proper results.

    Any ideas on this fellas?

    Thanks!

  7. #7
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Running macro on worksheet change is crashing workbook

    In the following context from the code you provided, 'Filename' is NOT a varialble so it would show up as blank.

    Please Login or Register  to view this content.
    I would recommend two things:
    a. Make sure there is a trailing Backslash on SPath.
    Please Login or Register  to view this content.
    b. Add 'Option Explicit' at the top of every code module:
    It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:
    Please Login or Register  to view this content.
    This option requires all variables to be declared and will give a compiler error for undeclared variables.

    You will get a lot of compiler errors at first, but it saves a lot of time when you have a rogue variable that is spelled incorrectly.

    --------------------------------------------
    Additionally, the following debugging tips may help you:
    a. Press 'F8' to single step (goes into subroutines and functions).
    b. Press SHIFT 'F8' to single step OVER subroutines and functions.
    c. Press CTRL 'F8' to stop at the line where the cursor is.
    d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
    e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
    output to the IMMEDIATE WINDOW.
    f. Select View > Locals to see all variables while debugging.
    g. To automatically set a BREAKPOINT at a certain location put in the line:
    'Debug.Assert False'
    h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
    if i >= 20 and xTV20 > 99.56 then
    Debug.Assert False
    endif
    i. A variable value will be displayed by putting the cursor over the variable name.

    To manually set a breakpoint, see http://www.wiseowl.co.uk/blog/s196/breakpoints.htm

    Lewis

  8. #8
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Running macro on worksheet change is crashing workbook

    Hi LJ,

    Thanks for the tips. So I applied your suggestions but the workbooks are still not saving in the file path indicated in cell B18. It doesn't seem like they're being saved anywhere.

    Any ideas how I can have the file path listed in a cell so that the workbook in the loop will be saved there?

    thanks!

  9. #9
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Running macro on worksheet change is crashing workbook

    It would be better if you can upload a sample. This will make us clear what's happening there. For me creating a sample to test your code is not possible. Sorry.

    You can remove those 2 lines from your code to see what's going wrong there by yourself.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    That's all I can do now for you without any sample.

  10. #10
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Running macro on worksheet change is crashing workbook

    Hi Groov,

    You have to meet us half way, and do some work to help us out. We need a sample file that demonstrates your problem. To fix a problem like this, we need to use data similar to real data in a Workbook similar to a real workbook.

    Very few responders will make the time and effort to create these things themselves, only to discover that their original assumptions were incorrect.

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Running macro on worksheet change is crashing workbook

    Hi,

    I would infer from your code that the SPath variable should be changing each time that you write to cell B15? If that is indeed the case, then that code needs to be inside the loop thus

    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Code running twice and crashing after workbook saves
    By ajam in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2016, 05:15 PM
  2. Excel Crashing without Error when running macro
    By robbeh989 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-04-2015, 07:08 PM
  3. [SOLVED] Running Macro and Worksheet Change on PROTECTED Sheet?
    By ksayet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2015, 01:07 PM
  4. Running Macro and Worksheet Change on PROTECTED Sheet?
    By ksayet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2015, 10:58 AM
  5. Running Macro and Worksheet Change on PROTECTED Sheet?
    By ksayet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2015, 10:16 AM
  6. Running Macro and Worksheet Change on PROTECTED Sheet?
    By ksayet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2015, 09:36 AM
  7. Excel 2010 keeps crashing when running a specific macro.
    By bean29 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-08-2013, 07:36 AM

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