how to run constantly updated macro on protected sheet -2003

merskamp

New Member
Joined
Sep 28, 2005
Messages
47
Hi there.
I have a worksheet which allows me to input sports scores.
These scores are also then populated in another area of the same sheet and my plan is to protect those duplicated cells so users don’t input data in the wrong set of cells.

The sports scores I input affect my league table also on the same sheet and I have the following code to auto update the table.

Code:
Private Sub Worksheet_Calculate()
         Application.EnableEvents = False
        Range("HC2:HH18").Sort Key1:=Range("HH3"), Order1:=xlDescending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    Application.EnableEvents = True
 End Sub

So my dilemma is when i protect the sheet - the autoupdating of the league table no longer happens - the protection is killing the macro i presume.

How can i get around this? The macro needs to run constantly forever rather than have me put in unprotect sheet, and then reprotect it at the end of the macro..
The sheetname is called Everything

If this is solvable, i have 3 other sheets in this workbook with a similar problem, macro wont autoupdate data.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
The macro needs to run constantly forever rather than have me put in unprotect sheet, and then reprotect it at the end of the macro..

Have you tried it? Betcha you don't even notice. ;)

Smitty
 
Upvote 0
Sorry Smitty have tried that and it fails as it never reprotects my sheet, possibly as the macro's design is that it doesnt actually ever stop running.
 
Upvote 0
I can't see any reason why this shouldn't work:

Code:
Private Sub Worksheet_Calculate() 
         Application.EnableEvents = False 
           ActiveSheet.Unprotect
             Range("HC2:HH18").Sort Key1:=Range("HH3"), Order1:=xlDescending, Header:= _ 
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
            DataOption1:=xlSortNormal
          ActiveSheet.Protect 
    Application.EnableEvents = True 
 End Sub

Although why are you disabling events? Is that really necessary?

Smitty
 
Upvote 0
Thanks this is actualyl workign, the problem is something in that the other sheets are protected and if they are protected it breaks the first sheet which copies data over there.
Your code works perfectly on the stand alone sheet.
Is there any way to change activesheet to say entire workbook?
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top