How can I lock worksheets while macros can still enter into them?

[Guest]

I want to lock worksheets for outside users. But when I do it and a macro
needs to go into the worksheet, the system gives an error. So is there a way
to lock worksheets while still allowing macros to enter into the sheets and
function properly?

 

[Paul B]

enginguven, you can protect the sheet with user interface only, like this

ActiveSheet.Protect UserInterfaceOnly:=True, password:="123"
or
Sheets("Sheet1").Protect UserInterfaceOnly:=True, Password:="123"

or use something like this

Const PW As String = "123" 'Change Password Here
ActiveSheet.Unprotect Password:=PW
'you code here
ActiveSheet.Protect Password:=PWEnd Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Indeed I don't want to lock these worksheets for editing. I misled you since
I want to HIDE sheets and don't encounter any problems with macros. I don'
want outside users to see the worksheets but want macros to function properly.

 

[Paul B]

enginguven, you can do most things on a sheet that is hidden, like this,

Sub test1()
With Sheets("Sheet1")
.Range("A1") = 25
.Range("A2") = "Test"
.Range("H3:H3000").ClearContents
.Range("G2").Font.ColorIndex = 3
End With
End Sub

But if you need to unhide the sheet for the macro to run you can let the
macro unhide the sheet, run your code and then hide the sheet again, if you
hide it with xlSheetVeryHidden you want see it listed when you go to format,
sheet, you will need to lock the VBA project to keep someone from unhiding
the sheet from there. The code below will do that.

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is to the left of the "File" menu this
will open the VBA editor, in Project Explorer right click on your workbook
name, if you don't see it press CTRL + r to open the Project Explorer then
select VBA project properties, protection, check lock project for viewing
and set a password. Press Alt and Q to close this window and go back to your
workbook and save and close the file. Be aware that this password can be
broken by third party software


Sub test2()
Application.ScreenUpdating = False
Sheets("Sheet1").Visible = True
'Your code here
Sheet1.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003