Protecting & unprotecting worksheets

P

pkley

I have 2 codes I stole from here, one to protect the selected sheets, the other to unprotect them. Obviously I need to be able to run them separatly. I added the first by right clicking the excel logo in upper left and pasted it in. But now how do I enter the second one? I'm really new at VBA and macros...
Public Sub ProtectSelectedSheets
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet
For Each wkSht In ActiveWindow.SelectedSheets
wkSht.Protect Password:=PWORD
Next wkSht
End Sub

Public Sub UnProtectMultipleSheets()
Const Pword As String = "drowssap"
Dim WkSht As Worksheet
For Each WkSht In Worksheets
WkSht.Unprotect Pword
Next WkSht
End Sub
 
K

Ken Wright

Put the code in your personal macro workbook. Hit Tools / Macros / Record New
Macro (Choose to storte it in your personal macro Workbook), type a 1 in any
cell, and hit the 'Stop recording' button.

Now Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you
need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Personal.xls)
Microsoft Excel Objects
Sheet1(Sheet1)
ThisWorkbook
Modules
Module1

Double click on the module1 and you should see some code like this:-

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/01/2004 by Ken Wright
'

'
ActiveCell.FormulaR1C1 = "1"
Range("D9").Select
End Sub

Select all that, delete it and then paste in both the pieces of code you found.
Now just hit File / Save Personal.xls and then do File / Close and return to
Microsoft Excel.

You can now use Tools / Macros / Macro / 'Pick one of the routines' whilst in
any other Excel file.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :)
----------------------------------------------------------------------------



pkley said:
I have 2 codes I stole from here, one to protect the selected sheets, the
other to unprotect them. Obviously I need to be able to run them separatly. I
added the first by right clicking the excel logo in upper left and pasted it in.
But now how do I enter the second one? I'm really new at VBA and macros...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top