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

1 reply

TrowaD
Posts
2888
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 16, 2022
515
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
Dear TrowaD,

Thank you very much, it works perfect.

Best regards,
Celina
0