+ Reply to Thread
Results 1 to 2 of 2

SOLVED: After running Macro crash on save

Hybrid View

  1. #1
    Registered User
    Join Date
    04-02-2015
    Location
    UK
    MS-Off Ver
    365
    Posts
    8

    SOLVED: After running Macro crash on save

    This is the macro I run via a button
    Sub STATUS_SORT()
    
    Dim oWorksheet As Worksheet
    Set oWorksheet = ActiveWorkbook.Worksheets("To-Do List Dan B-H")
    Dim oRangeSort As Range
    Dim oRangeKey As Range
    
    ' one range that includes all colums do sort
    Set oRangeSort = oWorksheet.Range("C3:C33")
    ' start of column with keys to sort
    Set oRangeKey = oWorksheet.Range("C3")
    
    ' custom sort order
    Dim sCustomList(1 To 5) As String
    sCustomList(1) = "IN PROGRESS"
    sCustomList(2) = "ON HOLD"
    sCustomList(3) = "NOT STARTED"
    sCustomList(4) = "ONGOING"
    sCustomList(5) = "COMPLETE"
    
    Application.AddCustomList ListArray:=sCustomList
    
    
    oWorksheet.Sort.SortFields.Clear
    oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    
    ' clean up
    Application.DeleteCustomList Application.CustomListCount
    Set oWorksheet = Nothing
    Set oRangeSort = Nothing
    Set oRangeKey = Nothing
    
    
    End Sub
    I can save the file fine until I run the macro, then the program crashes when it auto saves or I save it.
    Office 2013
    Windows 8.1 Pro
    Last edited by FlooringSales; 08-18-2017 at 06:37 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: After running Macro crash on save

    Hey, you're right. It crashes for me too, Office 2010 on Win7.

    It creates and deletes the custom list correctly. I Googled "excel crashes after deleting custom list" and found this webpage: https://social.msdn.microsoft.com/Fo...e?forum=isvvba

    The solution is to add another oWorksheet.Sort.SortFields.Clear after oRangeSort.Sort. This doesn't undo your sort, but it does clear the sorting criteria from the range, which is deleted after the macro. It was crashing because it couldn't find it.

    I tried the new code and it works.
    Design everything to be as simple as possible, but no simpler.

+ 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. The custom sort list causes my excel to crash after running and then trying to save
    By jonnytales in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-02-2024, 02:22 PM
  2. Excel Crash Upon Running Macro
    By MrPartial in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-12-2017, 05:34 PM
  3. App Crash when running Macros - Anyone know cause?
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-06-2015, 05:33 PM
  4. Help with running a macro on save
    By Whittle82 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2013, 12:54 PM
  5. After macro, crash if save
    By _Luca_ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-11-2008, 12:36 PM
  6. Save To Csv Running A Macro
    By stevekirk in forum Excel General
    Replies: 2
    Last Post: 08-11-2006, 09:42 AM
  7. Prevent Excel crash when running
    By rauxalacch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2005, 02:10 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