none
Calling AllowEditRange.Delete crashes Excel 2016 RRS feed

  • Question

  • Hello,

    Create a new workbook, add the second sheet to it, activate the first worksheet, press Alt+F11 to open the VBA IDE, paste the VBA macros below to ThisWorkbook:

    Public Sub Step1()
        Call Application.Worksheets("Sheet1").Protection.AllowEditRanges.Add("tmp1", Application.Worksheets("Sheet1").Cells)
        Call Application.Worksheets("Sheet2").Protection.AllowEditRanges.Add("tmp2", Application.Worksheets("Sheet2").Cells)
    End Sub
    
    Public Sub Step2()
        Call Application.Worksheets("Sheet1").Protection.AllowEditRanges("tmp1").Delete
        Call Application.Worksheets("Sheet2").Protection.AllowEditRanges("tmp2").Delete
    End Sub
    
    Run Step1. Running Step2 crashes Excel 2016 32bit build 8625.2121 and build 8711.2037 (this is Insider Fast). I'm ready to provide details, if required.

    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Thursday, November 9, 2017 1:36 PM

Answers

  • Hi Andrei Smolin,

    I would suggest you activate sheet before you delete its AllowEditRanges.

    Here is the example.

    Public Sub Step2()
        ThisWorkbook.Sheets("Sheet1").Activate
        Call Application.Worksheets("Sheet1").Protection.AllowEditRanges("tmp1").Delete
        ThisWorkbook.Sheets("Sheet2").Activate
        Call Application.Worksheets("Sheet2").Protection.AllowEditRanges("tmp2").Delete
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 10, 2017 2:03 AM
  • As Terry says need to activate the sheet but note in in earlier versions had to unprotect. To cater for all versions all versions can do both, something like this

    Sub delAER(ws As Worksheet, sTitle As String, Optional sPW As String, Optional bReprotect As Boolean)
    Dim aer As AllowEditRange
    
        On Error Resume Next
        Set aer = ws.Protection.AllowEditRanges(sTitle)
        On Error GoTo 0 ' or to handler
    
        If Not aer Is Nothing Then
            ws.Parent.Activate
            ws.Activate
            ws.Unprotect sPW
            aer.Delete
            If bReprotect Then
                ws.Protect sPW
            End If
        End If
    ' in the caller store and reset the original active worbook + sheet when done
    End Sub

     
    • Marked as answer by Andrei Smolin Monday, November 13, 2017 10:01 AM
    Friday, November 10, 2017 9:31 AM
    Moderator

All replies

  • Hi Andrei Smolin,

    I would suggest you activate sheet before you delete its AllowEditRanges.

    Here is the example.

    Public Sub Step2()
        ThisWorkbook.Sheets("Sheet1").Activate
        Call Application.Worksheets("Sheet1").Protection.AllowEditRanges("tmp1").Delete
        ThisWorkbook.Sheets("Sheet2").Activate
        Call Application.Worksheets("Sheet2").Protection.AllowEditRanges("tmp2").Delete
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 10, 2017 2:03 AM
  • As Terry says need to activate the sheet but note in in earlier versions had to unprotect. To cater for all versions all versions can do both, something like this

    Sub delAER(ws As Worksheet, sTitle As String, Optional sPW As String, Optional bReprotect As Boolean)
    Dim aer As AllowEditRange
    
        On Error Resume Next
        Set aer = ws.Protection.AllowEditRanges(sTitle)
        On Error GoTo 0 ' or to handler
    
        If Not aer Is Nothing Then
            ws.Parent.Activate
            ws.Activate
            ws.Unprotect sPW
            aer.Delete
            If bReprotect Then
                ws.Protect sPW
            End If
        End If
    ' in the caller store and reset the original active worbook + sheet when done
    End Sub

     
    • Marked as answer by Andrei Smolin Monday, November 13, 2017 10:01 AM
    Friday, November 10, 2017 9:31 AM
    Moderator
  • Hello Terry and Peter,

    Thank you very much!


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Monday, November 13, 2017 10:01 AM