.unprotect causing objects to flicker/hide

[aurelius89]

Hi,

I have a very long sub that does multiple things. It is long, complex and sensitive so I cannot post it.

However, while the sub is running, all objects on my sheet disappear (Images, command buttons, etc)
They reappear when the sub has finished.

I have

application.screenupdating = False

at the beginning and

application.screenupdating = True

at the end. However, I have discovered that if the sub contains

.unprotect

to unlock another sheet, then this is actually the cause and the screen flicker and disappearing objects are still shown to the user and isn't covered by the screenupdating statements

It is the same issue as this person:
https://stackoverflow.com/questions...cker-despite-application-screenupdating-false

I am unable to find a resolution. Maybe you guys know one?

 

[RoryA]

Have you tried protecting the sheet(s) at workbook open with the Userinterfaceonly:=True argument? That will allow most code to operate without having to unprotect the sheets.

 

[aurelius89]

Bingo! That works.

However, I guess pivot tables are one thing that it cant do?

I get the error "Cannot edit pivottable on protected sheet"

I have tried adding

AllowUsingPivotTables:=True

after the

[COLOR=#333333]Userinterfaceonly:=True [/COLOR]

argument but no joy.

It fails at this line:

Sheets("MySheet").PivotTables("MyPivot").PivotCache.Refresh

 

[RoryA]

Do you need to refresh the cache, or will refreshing the pivot table suffice? This should work:

Sheets("MySheet").PivotTables("MyPivot").RefreshTable

 

[aurelius89]

Ah yes no more error, thank you!