Annoying Problem With VBA Editor Crashing Excel

[Ark68]

I am working on an Excel VBA project and have recently started encountering a very annoying problem.
When I am testing my project, if I run into an error, or I stop to step through and make changes based on the results encountered while stepping through, and attempt to save these changes or corrections, Excel crashes. No error messages, it just folds up and restarts the project as if nothing happened. It's the original file, not a backed up one that is started, and the changes made are not reflected. (Assuming the save was unsuccessful).

If I don't execute my code in my project, and just go straight to making known changes or corrections and save, I'm fine. It's only after my code has been executed.

I know it's a crap shoot, but is there any way I can determine what is causing this crash?

 

[Corbett]

In your sort_pda function could you try putting the .Unprotect directly after the With ws_master statement just in case the unprotection is somehow affecting the range definition.

 

[Ark68]

Hi Rory. Thanks for bringing your expertise to the table. Let me see if I can answer your questions ...

Does it make any difference if you use the worksheet.sort object rather than the range.sort method, or at least clear any existing sortfields set on the sheet sort before you do your range sort?

I'm not quite sure how to use the worksheet.sort object. In fact, that might be a whole new concept for me. Willing to give it a try with a bit of research and coaching. Whether or not I have any sort fields in this worksheet is uncertain. I've been working on this project for 3 years now and there are times I forget what I may have done previously. But this worksheet in particular is simply a "receiver" of external data from other data sources that are usually brought in already in a sorted order. I think this is the only scenario where I am actually sorting on the worksheet. I can see where being able to provide the whole package would be of such value for those wishing to help.

Also, are any of the controls on your userform linked directly to ranges on worksheets using rowsource or controlsource?

I'm looking at my worksheet to identify all the features. Understand that this worksheet is acting as an interactive interface of "imported and preprocessed" data and the user, allowing the user to change certain values of that data in preparation of further processing and report generation. No of the controls on the this particular active userform are tied to any data on the worksheet except for one or two static labels referring to static data in the workbook. (eg date)

The worksheet is normally protected with the majority of cells locked to prevent the user from trying to access data that they should not be changing.
The cells the user can access are unlocked and behave in different manners depending on the target cell. For instance, some cells have associated double click code (ie opens up a form to allow the user to modify data specific to the value in that cell), others when clicked provide the user with a dropdown list (cell list validation based on a list created as part of that code ie cells do not retain their validation, it is recreated each time the target cell is clicked).

One thing that I'm starting to wonder, based on my minimal knowledge of protected cells, the cells in the range I am sorting are locked. But, my worksheet is unprotected (should be) prior to performing the sort), and then protected again after the sort. Could this be the problem? I assumed that unprotecting a sheet disables the locked function of cells.

 

[RoryA]

Here's a simple example of the Sort object:

    Dim dataSheet As Worksheet
    Set dataSheet = ActiveWorkbook.Worksheets("Sheet1")
    Dim dataSort As Sort
    Set dataSort = dataSheet.Sort
    
    With dataSort
        With .SortFields
            ' important to clear any existing sort fields before setting up a new sort
            .Clear
            .Add Key:=dataSheet.Range("a1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=dataSheet.Range("b1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange dataSheet.Range("A1:C100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

No of the controls on the this particular active userform are tied to any data on the worksheet except for one or two static labels referring to static data in the workbook

You can't actually link a label to a range directly, so not sure what you mean by that.

Cell protection has no effect if a sheet is not protected.

 

[Ark68]

Thanks Rory, fingers crossed. I'll try to adapt to your example, and try *Corbett's suggestion as well. I'll report back.

You can't actually link a label to a range directly, so not sure what you mean by that.

I probably should have said a textbox that has been disabled/locked.

I was reeeeeally hoping that it might be as simple as unlocking that range of cells. Oh well.

 

[Ark68]

Hi Rory! Your solution worked! Yay!!! You made my day I'm so happy. If I could repay you with a beer or a coffee, I'd be honoured.
Now, is it worth explaining why this method worked over the other? Or should I just be content with knowing it works?

Sub pda_sort()
    Dim dataSort As Sort
    Set dataSort = ws_master.Sort
    
    With ws_master
        .Unprotect
        'rng_svctop = Application.WorksheetFunction.Match("ADD", .Columns(1)) + 1
        'rng_svcbot = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1)) - 3
        
        rng_svctop = Application.Match("ADD", .Columns(1), 0) + 1
        If IsError(rng_svctop) = True Then MsgBox CLng(Split(CStr(rng_svctop), " ")(1))
            
        rng_svcbot = Application.Match("Facility Maintenance Activities", .Columns(1), 0) - 3
        If IsError(rng_svcbot) = True Then MsgBox CLng(Split(CStr(rng_svcbot), " ")(1))
        
        Set rng_pdaservices = .Range("A" & rng_svctop & ":Q" & rng_svcbot) '& ":R" & rng_svcbot)
        'With rng_pdaservices
        '    .Cells.Interior.ColorIndex = 36
        '    .Sort key1:=.Range("H" & rng_svctop), order1:=xlAscending, Header:=xlNo
        'End With
        With dataSort
            With .SortFields
            ' important to clear any existing sort fields before setting up a new sort
                .Clear
                .Add Key:=ws_master.Range("H" & rng_svctop), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            End With
            .SetRange rng_pdaservices
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Protect
    End With
    'MsgBox "PDA Sservices range sorted."
End Sub

 

[RoryA]

It was a bit of a punt to be honest. The only thing I could think of was that perhaps your sort operation was breaking an existing sort object (eg if you had partial overlap), so I figured it was worth clearing any existing sort and replacing it completely with your new one. Just glad it worked.