Previous Topic Next Topic

Delete AllowEditeRange by VBA command

[Solved/Closed]
Report
-
 Celina -
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

Answer 1 / 1
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
 487
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
Report

Dear TrowaD,

Thank you very much, it works perfect.

Best regards,
Celina
Ask a question