Annoying Problem With VBA Editor Crashing Excel

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 22, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Here's a simple example of the Sort object:

Code:
    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.
 
Solution

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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. :)
 

Forum statistics

Threads
1,147,482
Messages
5,741,404
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top