Previous Topic Next Topic

Delete AllowEditeRange by VBA command

Solved/Closed
Celina - Apr 20, 2011 at 12:04 PM
 Celina - Apr 26, 2011 at 07:00 AM
Dear All,
Can anyone help me to correct the following code:

Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim aer As AllowEditRange
For Each WS In Worksheets
For Each aer In ActiveSheet.Protection.AllowEditRanges
aer.Delete
Next aer
Next WS
End Sub

This Code I do it to remove all AllowEditeRanges in every sheet in excel, but unfortunately it works only in the sheet that the command button exist in and NOT for every sheet.
So can anyone help me to modify this code.
Thanks in advance.

Celina
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 21, 2011 at 09:26 AM
Hi Celina,

Added this codeline: ws.select

Now your code looks like: 
Sub test()
Dim WS As Worksheet
Dim aer As AllowEditRange
For Each WS In Worksheets
WS.Select
For Each aer In ActiveSheet.Protection.AllowEditRanges
aer.Delete
Next aer
Next WS
End Sub

Best regards,
Trowa
0
Celina
Apr 26, 2011 at 07:00 AM
Dear TrowaD,

Thank you very much, it works perfect.

Best regards,
Celina
0