none
Excel 2010 Pro (64bit) Crashes on Change By Val Sub RRS feed

  • Question

  • Guys I have tried for 2 days to resolve this issue without success.

    Frankly, I can not even trap WHERE the error occurs in the sub.

    I have 2 WorkBooks. One is for testing code and the other is for Production. (They do not contain the same data)

    I have written a SelectionChange sub which works perfectly in the Test WorkBook but when I copy it to the production workbook and click in ANY cell in the production workbook crashes Excel.

    Finally, in the Production workbook, I stripped all the code out of the SelectionChange sub and replaced it with a MsgBox and again when I click in ANY cell Excel crashes and no msgbox message appearing. Yes, this is the only VBA code in the worksheet. Yes, I have put STOPs in the code but processing never gets to the STOPs before Excel crashes.

    When I delete the SelectionChange sub the Production Workbook does not crash. It seems just by placing the SelectionChange in the WorkBook it crashes Excel when a cell is selected.

    Yep even rebooted the system just in case Excel got corrupted somehow.

    Has anybody got any idea as to how to resolve this crazy problem?

    Here are some other variations that I have tried and they too crash Excel.

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

       MsgBox(“Test SelectionChange Target As Excel.Range”)

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

       MsgBox(“Test SelectionChange ByVal Target As Range”)

    End Sub

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

       MsgBox(“Test Change ByVal Target As Excel.Range”)

    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)

       MsgBox(“Test Change ByVal Target As Range”)

    End Sub


    A guy with an insatiable appetite for knowledge. No animals or trees were harmed in the creation of this message; however, countless electrons were forced from their homes and severely inconvenienced on their long journey to your inbox.

    Friday, December 30, 2016 2:46 AM

Answers

  • As I mentioned, I've run into this before. This is the Hammer Solution, but akes less than an afternoon to do.

    After exhausting errant Formula checks (the function no longer exists), checked ALL Conditional Formatting, Data Validation, Defined Names, visible or hidden that are formula's:

    (1) Save your original in a safe place.

    (2) In the IDE (Visual Basic Editor), Save copies of all the code modules, Classes, and the Class code associated with the worksheets/workbook (right-click the name and select Export, or drag/drop all but the worksheet/workbook modules.

    (3) Save the file as xlsx. Kills all code. Come to think of it, you might just try to save as xlsm or xlsb first. If that doesn't work then continue on.

    (4) Close & Re-Open Excel, open the xlsx file you just saved.

    (5) Add a couple of your simple tests w/ a msgbox only. use the selctions in a worksheet and workbook for the SelectionChange event.

    (6) Save as xlsm or xlsb. Close & re-open excel & the file. Then test it by clicking around.

    (7) If all is well, you can delete the test events code and import your code back into the IDE module by module and class by class. I add the worksheet/workbook code last. You can check functionality after you import each module if you want.

    Hope this helps get you over the hurdle.


    -MainSleuth


    Friday, December 30, 2016 3:18 PM

All replies


  • I have written a SelectionChange sub which works perfectly in the Test WorkBook but when I copy it to the production workbook and click in ANY cell in the production workbook crashes Excel.

    Finally, in the Production workbook, I stripped all the code out of the SelectionChange sub and replaced it with a MsgBox and again when I click in ANY cell Excel crashes and no msgbox message

    Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    A macro to anonymize data in selected cells can be downloaded here:
    https://dl.dropboxusercontent.com/u/35239054/modAnonymize.bas

    Andreas.

    Friday, December 30, 2016 8:37 AM
  • I'm a little confused, but I'm mid into my first cup of coffee.

    Are you putting the code into the Workbook code object or the worksheet code object? The event signatures are different:

    For Worksheet, as you have it:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    For Workbook, it has to include the Worksheet reference:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If that's not it, I'll follow with what I did when I encountered a similar problem.


    -MainSleuth

    Friday, December 30, 2016 3:06 PM
  • As I mentioned, I've run into this before. This is the Hammer Solution, but akes less than an afternoon to do.

    After exhausting errant Formula checks (the function no longer exists), checked ALL Conditional Formatting, Data Validation, Defined Names, visible or hidden that are formula's:

    (1) Save your original in a safe place.

    (2) In the IDE (Visual Basic Editor), Save copies of all the code modules, Classes, and the Class code associated with the worksheets/workbook (right-click the name and select Export, or drag/drop all but the worksheet/workbook modules.

    (3) Save the file as xlsx. Kills all code. Come to think of it, you might just try to save as xlsm or xlsb first. If that doesn't work then continue on.

    (4) Close & Re-Open Excel, open the xlsx file you just saved.

    (5) Add a couple of your simple tests w/ a msgbox only. use the selctions in a worksheet and workbook for the SelectionChange event.

    (6) Save as xlsm or xlsb. Close & re-open excel & the file. Then test it by clicking around.

    (7) If all is well, you can delete the test events code and import your code back into the IDE module by module and class by class. I add the worksheet/workbook code last. You can check functionality after you import each module if you want.

    Hope this helps get you over the hurdle.


    -MainSleuth


    Friday, December 30, 2016 3:18 PM
  • Andreas, thanks for the offer and as a last resort I will take you up on the upload. However, because of the size and preparatory nature of some material I would like to hold off until I exhaust other possibilities.


    A guy with an insatiable appetite for knowledge. . No animals or trees were harmed in the creation of this message; however, countless electrons were forced from their homes and severely inconvenienced on their long journey to your inbox.

    Friday, December 30, 2016 8:56 PM
  • MainSleuth, I certainly understand the nature of your confusion.  I used workbook in the problem description to clearly indicate the worksheets were in different workbooks.  But yes the code was written for a specific work sheet. Although, it was kind for you to provide the code to be used in workbooks which I did not know and shall keep as a reference.

    The “Hammer Solution” :) looks very promising and I will start-in on that procedure later today.  Also, I have copied it to my permanent “Tips & Debug” folder. It could also prove very useful in the future.


    A guy with an insatiable appetite for knowledge. . No animals or trees were harmed in the creation of this message; however, countless electrons were forced from their homes and severely inconvenienced on their long journey to your inbox.

    Friday, December 30, 2016 8:57 PM
  • Thanks MainSleuth!!!

    First, the workbook was already in the xlsm format so I saved it in the xlsb format and gave it a go.  It produced the same results and crashed Excel.

    When working with the .xlsx file and with the simple msgbox test (in the SelectionChange by Val sub) I AM to click around the problem sheet without it crashing Excel. PROGRESS, Yea...   Yes, I will start adding sections and testing after each addition until I find the problem code.

    As a professional courtesy I will post where the problem arose if I can tag it.

    Many thanks to you for your guidance and help.


    A guy with an insatiable appetite for knowledge. . No animals or trees were harmed in the creation of this message; however, countless electrons were forced from their homes and severely inconvenienced on their long journey to your inbox.

    Friday, December 30, 2016 10:41 PM
  • Excellent! Glad you now have some leverage on the problem and with it, a glimmer of hope for the new year.

    -MainSleuth

    Saturday, December 31, 2016 8:29 AM