Pausing a looped process running if a second macro is run

[B16NKO]

I've got an issue with a simple piece of code which runs a constant loop to protect a specific worksheet, unless you double click on a cell that is linked to data in another sheet. Which then takes you to that data and reprotects the sheet which is running the loop.

My problem is there is a macro that is manually run periodically to refresh the data on the sheet that is protected. Obviously if it is trying to copy and paste to a sheet that is constantly protected I get the usual "The cell or chart you are trying to modify is protected..." error.

Is there a way I can insert some code into the refresh macro to halt the looped code temporarily?

Many Thanks

NKO

[B16NKO]

All,

Is it possible to code a pause in some code based on another macro being executed.

I've got the following piece of code in a worksheet ("input") to protect it:

Sub Worksheetprotection()
Sheet1.Protect Password:="data"
End Sub

however when I kick off a macro on the input sheet, which is called Refresh_for_Current_Month it won't update because of the above code locking the sheet.

Obviously I would normally just write a lock and unlock into the Refresh macro but this doesn't work as there is the need for some other code in the same worksheet to allow the user to drill down into some source data.

What I ideally need is a line of code to say Pause if the Refresh_for_Current_Month macro is running...any ideas how?!

Thanks as always

NKO

[DonkeyOte]

B16NKO - please note your 2nd post is a dupe thread (in effect) - given the detail provided is slightly different I have on this occasion merged the two.

Going forward please stick to one thread - if you need to clarify points made do so via a suitable reply post.

[B16NKO]

Apologies, I believed I was asking for a different solution to the same problem, just trying to solve an issue thats been a bit of a thorn in my side. Any help would be much appreciated.

NKO

[mikerickson]

Have you tried setting the UserInterfaceOnly of in the Protect method to True?

[B16NKO]

Hi Mike,

I've tried the below piece of code but to no avail. Started to get a bit fed up now...

Private Sub Worksheet_Activate()
Sheet1.Protect Password:="data", UserInterfaceOnly:=True
End Sub

My second thought would be if I could protect the sheet only when it is selected by the user and unprotect it when any other sheet in th workbook is active, but no idea as to how i'd code it.

Your help is much appreciated.

NKO