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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
 

merskamp

New Member
Joined
Sep 28, 2005
Messages
47
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.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
 

merskamp

New Member
Joined
Sep 28, 2005
Messages
47
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,261
Messages
5,623,692
Members
415,983
Latest member
MusicMan

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
Top