Allow Users to Edit Ranges

[Pinaceous]

Good Day All,


Is there a way to produce the "Allow Users to Edit Ranges" status icon as depicted in the picture below through shortcut keyboard typing or vba?

The reason why I'm asking is because I've modified my ribbon and did not want to undo it to accomplish this particular task.


Please let me know.

Thank you!
pinaceous

 

[NateSC]

You could add it to the Quick Access Toolbar or use Alt-R-U-1 (you don't have to hold them all down, just press them in order).

 

[Pinaceous]

Hi NateSC,

Wow, that really helps so much! Let me give it a go!

Thanks!
Pinaceous

 

[Pinaceous]

Hi NateSC,

The Alt-R-U-1 does pull up the "Allow Users to Edit Ranges" status icon but in my case since I have the ribbon blocked it does not work upon my sheet.

Do you have a vba suggestion to pull up that icon?

Please let me know.

Thank you!

Respectfully,
pinaceous

 

[Skyybot]

Would something like this work?

Sub AllowEdit()
Dim wb As Workbook, sht As Worksheet, rng As Range
Set wb = ThisWorkbook: Set sht = wb.Sheets(1)
Set rng = sht.Range("A1:E5")
sht.Protection.AllowEditRanges.Add "User Editable", rng, "Password"
End Sub

 

[Pinaceous]

Hey Skyybot,

That did change the Allowable Edit Ranges and I appreciate that!

I noticed that it cannot be run twice because it cannot replace what is there.

Do you know if there is a way to delete any existing ranges, if they are present upon that sheet?

Thank you!
pinaceous

 

[Skyybot]

Sub DeleteAllowEditRanges()
Dim wb As Workbook, sht As Worksheet, i As Integer, x As Integer
Set wb = ThisWorkbook: Set sht = wb.Sheets(1)
i = sht.Protection.AllowEditRanges.Count
For x = 1 To i
    sht.Protection.AllowEditRanges(x).Delete
Next x
End Sub

 

[Pinaceous]

Sub DeleteAllowEditRanges()
Dim wb As Workbook, sht As Worksheet, i As Integer, x As Integer
Set wb = ThisWorkbook: Set sht = wb.Sheets(1)
i = sht.Protection.AllowEditRanges.Count
For x = 1 To i
    sht.Protection.AllowEditRanges(x).Delete
Next x
End Sub

Thank you so much Skyybot!