Macros on protected sheets?

[Karen Brown]

Hi

I have a macro which i need users of the worksheet to run themselves i
order to update the data validation lists.

My sheet is protected so that they cannot mess around with th
formulas.

Is there any way i can run a macro on a protected sheet without puttin
the password in manually?

Any ideas much appreciated

 

[Ron de Bruin]

Hi Karen

You can unprotect your sheet in the code
and protect it again after your code is ready.

Sheets("Sheet1").Unprotect "ron"
' your code
Sheets("Sheet1").Protect "ron"


But I like this way.

Protect your worksheets with code like this
Place this in the Thisworkbook module.

The macro's will be working now
It will only protect the userfaceonly

Private Sub Workbook_Open()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect userinterfaceonly:=True
Next sh
Application.ScreenUpdating = True
End Sub

 

[Guest]

Yes, I did it all the time.

'At the beginning of the macro add:
ActiveSheet.Unprotect Password:="yourPassword"

'Then put your code here.

'Then put this at the end of the macro

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="yourPassword"

Be sure to protect the macro or people will be able to see
the password for the sheets

 

[Daniel]

Try the following:
Sheets("worksheetname").Select
ActiveSheet.Unprotect ("passwordname")

Don't forget to reprotect the worksheet at the end of the
module. This is done by:
ActiveWorksheet.Protect ("passwordname")

 

[Dave Peterson]

Sometimes, your macro can do things that the user can't--if you protect the
sheet in code:

In a general module:

Option Explicit
Sub auto_open()

With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With

End Sub

(that userinterfaceonly stuff is very important to you.)

But there are a few things that can't be done this way. You have to unprotect
and then reprotect.