Macro and Protection

R

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?
 
B

broro183

Hi Roberto
Roberto said:
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, unloc
it
and fix it.
Can a Macro do it, as it can do it when the sheet is protected, bu
pword
free, but is there a way to unlock it, fix the problem and th
password
protect it again?
Click to expand...

Yes, if the following code is entered into the "this workbook" modul
(press [alt+F11] in Excel & then [view - project explorer], a macr
could be assigned to a button to unprotect the sheet (with or without
password), fix the problem & reprotect the sheet.

Thisworkbook code:
Private Sub Workbook_Open()
Dim Password As String: Password = "secret" ' change to persona
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 guidanc
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 th
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 wor
before I'll check for any questions. If it's not, hopefully someone els
will respond to your questions.

hth, good luck,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..
 
R

Roberto Bumbalo

Hi Roberto
Roberto said:
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?
Click to expand...

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
Click to expand...


Thanks, Boro!
 
B

broro183

Hi Roberto,
Pleased I could help :)

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