Removing Multiple Edit Ranges in Worksheet.Activate

[bja2013]

Hi.

I am using the worksheet open event as the trigger to copy and paste editable ranges from other worksheets into a 'summary sheet' but I do not want users to be able to edit the ranges in the resultant 'summary sheet'.

I can get the data okay but the open event seems to be causing looping problems with my attempts to protect the resultant cells so the 'summary sheet' cells remain editable.

I'm using the following to remove the editable ranges:

For Each aer In s1.Protection.AllowEditRanges
aer.Delete
Next

​

It works fine when I step through it but when I run it, the editable ranges are still there so the open.event must recapture the source cells as editable and I cannot work out how to switch off the event and still have it work.

I've tried using the following to try and disable the event activation without success:

Application.EnableEvents = True/False
​

Clearly my logic, the order that I am doing it, or the location where I put the code is wrong.

Any help would be appreciated.

TIA.

Cheers,
BJA

 

[AlphaFrog]

Can you show your whole code?

 

[bja2013]

Hi AlphaFrog,

The following shows the main bits that grab the ranges I am after before bringing them to the 'Summary' sheet, but the important part is the ability to delete the AllowEditRanges entries after moving the ranges during the 'Summary' Worksheet_Activate event.

Cheers,
BJA

--------------------------------------------------------------------------------------------
Private Sub Worksheet_Activate()

Call updateSWSummarySheet

End Sub
--------------------------------------------------------------------------------------------

Sub updateSWSummarySheet()

Application.ScreenUpdating = False
Dim s1 As Excel.Worksheet
Dim s2 As Excel.Worksheet
Dim s3 As Excel.Worksheet

Dim iLastRowS1 As Integer
Dim iLastRowS2 As Integer
Dim iLastRowS3 As Integer

Dim i As Integer
Dim a As Integer
Dim aer As AllowEditRange

Dim rangeS1 As Range
Dim rangeS2 As Range
Dim rangeS3 As Range

Set s1 = Sheets("Summary")
Set s2 = Sheets("ABC")
Set s3 = Sheets("DEF")


On Error Resume Next
s1.Unprotect Password:= ""
iLastRowS1 = s1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
iLastRowS2 = s2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
iLastRowS3 = s3.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Set rangeS1 = s1.Range("A5:S" & iLastRowS1 + 1)
Set rangeS2 = s2.Range("A5:S" & iLastRowS2 + 1)
Set rangeS3 = s3.Range("A5:S" & iLastRowS3 + 1)

rangeS1.Delete Shift:=xlUp
rangeS2.Copy s1.Cells(5, 1)
iLastRowS1 = s1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
rangeS3.Copy s1.Cells(iLastRowS1 + 2, 1)
iLastRowS1 = s1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For Each aer In s1.Protection.AllowEditRanges
aer.Delete
Next
s1.Protect Password:=""

Application.ScreenUpdating = True

End Sub

 

[AlphaFrog]

Are the cells unlocked? You could edit unlocked cells on a protected sheet. Deleting AllowEditRanges doesn't affect the Locked property of cells. They are separate properties.

 

[bja2013]

Hi AlphaFrog.

What I am doing is copying ranges that include locked and unlocked cells to a new worksheet where ALL the cells need to be locked. The target sheet is a summary sheet and I only want users entering data in the source worksheets.

In the code I showed you before, I have the cells unprotected at the point where I try and remove the aer items but whether protection is on or off, it's as though the 'worksheet_activate' event is continuing to populate the aer list after I remove them.

When I step through the code, the aer entries are deleted correctly and the affected cells are no longer editable. This is my desired outcome. When I activate the worksheet (by clicking on the worksheet tab), the code runs okay and the data ranges are copied correctly but the cells stay unprotected as in the original source worksheets. This is undesirable.

I hope this clarifies things.

Cheers,
BJA