Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting & unprotecting worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting & unprotecting worksheets
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" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting and Unprotecting several worksheets at one time | Excel Discussion (Misc queries) | |||
protecting and unprotecting | Excel Worksheet Functions | |||
Macro for protecting and unprotecting multiple worksheets | Excel Discussion (Misc queries) | |||
PROTECTING/UNPROTECTING SHEETS | Excel Discussion (Misc queries) | |||
Protecting and Unprotecting multiple sheets | Excel Programming |