Macros on protected sheets?
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Macros on protected sheets?
 

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Karen Brown" wrote in message ...

Hi

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

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

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

Any ideas much appreciated.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Macros on protected sheets?
 

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

-----Original Message-----

Hi

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

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

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

Any ideas much appreciated.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Macros on protected sheets?
 

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")



-----Original Message-----

Hi

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

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

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

Any ideas much appreciated.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Macros on protected sheets?
 

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.



Karen Brown wrote:

Hi

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

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

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

Any ideas much appreciated.

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson