Macro and Protection

[Roberto Bumbalo]

Hi,
I am responsible for a spreadsheet that calculates the cost of sales of a
chain of restaurants in the UK. The chain is linked via intranet and a
remote desktop. The spreadsheet is fairly easy to use, however due to the
fact many people using it aren'computer literate, very often the ss fails
with the REF! error message.

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?

[broro183]

Hi Roberto

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...

[Roberto Bumbalo]

On 31/1/06 12:58, in article
[email protected], "broro183"
<[email protected]> wrote:

> 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...
> I


Thanks, Boro!

[broro183]

Hi Roberto,
Pleased I could help :-)

Rob Brockett
NZ
Always learning & the best way to learn is to experience...