Can I run macros on a protected worksheet?

[Mel Smith]

I have a workbook of 6 worksheets and in them are some 76 macros. I have protected each worksheet's cells that contain formulas. The macros I have set up no longer run now that I have set up the protection. Is there a simple way round this?

Mel

 

[MARK858]

Turn the protection off at the start of the code, turn it on again at the end of the code

Sheets("your_sheet_name").UnProtect "your password"
 'rest of your code
Sheets("your_sheet_name").Protect "your password"

There is another method if you are password protecting using code initially

 

[Mel Smith]

Hello Mark,

On each sheet there are 15 or so macros. Should I copy your code into each macro?

Mel

 

[MARK858]

Yes, you'll need to do it on every code unless you are using one code to call the others.

 

[Mel Smith]

OK. Many thanks. Now for a lot of copying and pasting!

Mel

 

[MARK858]

Your welcome (obviously you need to change the sheet name and password to suit)

A bit concerning if you have 15 event macro's in each sheet (assuming that they are event macro's and not regular macro's)

 

[Mel Smith]

What is wrong with this code? This macro refuses to run?
Sub T20_Bowling_Total_Runs()
Dim rng As Range
Dim cell As Range
Dim maxVal As Double
Sheets("T20 bowling").Unprotect "WWW"

Set rng = Range("CH4:CH23")

maxVal = rng.Cells(1).Value

For Each cell In rng
If cell.Value > maxVal Then
maxVal = cell.Value
End If
Next cell

For Each cell In rng
If cell.Value = maxVal Then
cell.Interior.Color = RGB(255, 255, 0)
End If
Next cell
Sheets("T20 bowling").Protect "WWW"
End Sub


Mel

 

[Mel Smith]

All sorted - thanks Mike

Mel

 

[MARK858]

Mel, is this one of the macro's in the sheet? if yes, is exactly the same macro in the other sheets and is the password the same for the other sheets?

 

[Mel Smith]

All sorted, now. Many thanks.

Mel