Re: Macro and Protection
- From: broro183 <broro183.22i2hy_1138712401.2068@xxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 31 Jan 2006 06:58:13 -0600
Hi Roberto
Roberto Bumbalo Wrote:
>
> It always happens when people drag and drop values. It is very easy to
> 'repair', but all formulas are locked, so I have to open the ss, unlock
> it
> and fix it.
> Can a Macro do it, as it can do it when the sheet is protected, but
> pword
> free, but is there a way to unlock it, fix the problem and the
> password
> protect it again?
Yes, if the following code is entered into the "this workbook" module
(press [alt+F11] in Excel & then [view - project explorer], a macro
could be assigned to a button to unprotect the sheet (with or without a
password), fix the problem & reprotect the sheet.
Thisworkbook code:
Private Sub Workbook_Open()
Dim Password As String: Password = "secret" ' change to personal
preference
Dim ws As Worksheet
'To password protect each sheet in the workbook
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=Password, *UserInterfaceOnly:=True* ' for guidance
on options search "protect method" in VBE Help
Next ws
end sub
When this is entered you can then record a macro [tools - macros -
record new macro] of the process you go through when you repair the
ref# error, assign this to a command button & it'll be problem solved!
I hope the above is enough detail for you as I'm off to bed & then work
before I'll check for any questions. If it's not, hopefully someone else
will respond to your questions.
hth, good luck,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30068
View this thread: http://www.excelforum.com/showthread.php?threadid=506721
.
- References:
- Macro and Protection
- From: Roberto Bumbalo
- Macro and Protection
- Prev by Date: Re: Calling on a macro within a macro!
- Next by Date: Re: Calling on a macro within a macro!
- Previous by thread: Macro and Protection
- Next by thread: help urgent
- Index(es):
Relevant Pages
|
