How to run macro on Excel 2007 protected sheet?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Valter - 27 Aug 2007 22:50 GMT

I have a Excel 2007 file, and want to run macro on the protected sheet. It
gives error at the very begining. I've found some sugestion using the
following code:

Sheets("Purchase Order Template").unprotect Password:="password"

and

Sheets("Purchase Order Template").protect Password:="password"

but it still doesn't work. Any idea what might do the trick? Thanks :)

Reply to this Message

Dave Peterson - 27 Aug 2007 23:04 GMT

Are you sure that the name of the worksheet is "purchase order template" (watch
for extra spaces!)

Are you sure that the password is really "password" (watch upper/lower case)

What happens when you try to run it?

> I have a Excel 2007 file, and want to run macro on the protected sheet. It
> gives error at the very begining. I've found some sugestion using the
[quoted text clipped - 7 lines]
>
> but it still doesn't work. Any idea what might do the trick? Thanks :)

Signature

Dave Peterson

Reply to this Message

Valter - 28 Aug 2007 17:54 GMT

Hi Dave,

yes, the sheet name is sorrect, and password is correct, no extra spaces, no
upper/lower cases mistakes

When it runs I get run-time error 1004

Reply to this Message

Dave Peterson - 28 Aug 2007 18:34 GMT

Can you unprotect the worksheet manually? Maybe Hanno's suggestion is a better
fit.

> Hi Dave,
>
> yes, the sheet name is sorrect, and password is correct, no extra spaces, no
> upper/lower cases mistakes
>
> When it runs I get run-time error 1004

Signature

Dave Peterson

Reply to this Message

Valter - 28 Aug 2007 23:12 GMT

Hi Dave,

well, I can unprotect the sheet manually, that's no problem, but since the
file will be used by many others outside my domain, it's kinda unpractical to
share the password with all of them, then what's the use of the protection :)
...

It's purchase order that automatically calculates prices and discounts basen
on the user input, and presents the figures in 3 different currencies, again
based on the user shoice. So I have settings sheet where user should type in
data (company, address, discount in %, pick the currecncy) and the rest is
done on the Purchase Order Sheet. Data typed in settings sheet is replicated
to Purchase Order Sheet. The problem comes when something needs to be changed
in the protected cell(s) on the Purchase Order Sheet.

Reply to this Message

Dave Peterson - 28 Aug 2007 23:17 GMT

Where is the code?

Is it in a General module or behind a worksheet?

maybe...
me.parent.Sheets("Purchase Order Template").unprotect Password:="password"

But I'm out of guesses.

> Hi Dave,
>
[quoted text clipped - 10 lines]
> to Purchase Order Sheet. The problem comes when something needs to be changed
> in the protected cell(s) on the Purchase Order Sheet.

Signature

Dave Peterson

Reply to this Message

Valter - 28 Aug 2007 23:32 GMT

Hi Dave,

now that's way above me :) not quite sure what should I answer, but here is
the whole code.

Sub USDPrice()
'
' USDPrice Macro
'

'
Sheets("Purchase Order Template").Select
Range("H20").Select
ActiveCell.FormulaR1C1 = " PRICING IN USD $"
Range("G31").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC[-4],AcronisProducts,3,FALSE)),"""",VLOOKUP(RC[-4],AcronisProducts,3,FALSE))"
Range("G31").Select
Selection.AutoFill Destination:=Range("G31:G36")
Range("G31:G36").Select
Range("H31").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC[-5],AcronisProducts,10,FALSE)),"""",VLOOKUP(RC[-5],AcronisProducts,10,FALSE))"
Range("H31").Select
Selection.AutoFill Destination:=Range("H31:H36")
Range("H31:H36").Select
Range("G31:H36,J31:J37").Select
Range("J31").Activate
Selection.NumberFormat = "[$$-409]#,##0.00"
Range("D11:F11").Select
Sheets("Settings").Select
Range("I5").Select
End Sub
Sub EUROPrice()
'
' EUROPrice Macro
'

'
Sheets("Purchase Order Template").Select
Range("H20").Select
ActiveCell.FormulaR1C1 = " PRICING IN EURO €"
Range("G31").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC[-4],AcronisProducts,4,FALSE)),"""",VLOOKUP(RC[-4],AcronisProducts,4,FALSE))"
Range("G31").Select
Selection.AutoFill Destination:=Range("G31:G36")
Range("G31:G36").Select
Range("H31").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC[-5],AcronisProducts,11,FALSE)),"""",VLOOKUP(RC[-5],AcronisProducts,11,FALSE))"
Range("H31").Select
Selection.AutoFill Destination:=Range("H31:H36")
Range("H31:H36").Select
Range("G31:H36,J31:J37").Select
Range("J31").Activate
Selection.NumberFormat = "#,##0.00_- [$€-1]"
Range("D11:F11").Select
Sheets("Settings").Select
Range("I5").Select
End Sub
Sub POUNDPrice()
'
' POUNDPrice Macro
'

'
Sheets("Purchase Order Template").Select
Range("H20").Select
ActiveCell.FormulaR1C1 = " PRICING IN UK POUND £"
Range("G31").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC[-4],AcronisProducts,5,FALSE)),"""",VLOOKUP(RC[-4],AcronisProducts,5,FALSE))"
Range("G31").Select
Selection.AutoFill Destination:=Range("G31:G36")
Range("G31:G36").Select
Range("H31").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC[-5],AcronisProducts,12,FALSE)),"""",VLOOKUP(RC[-5],AcronisProducts,12,FALSE))"
Range("H31").Select
Selection.AutoFill Destination:=Range("H31:H36")
Range("H31:H36").Select
Range("G31:H36,J31:J37").Select
Range("J31").Activate
Selection.NumberFormat = "[$£-809]#,##0.00"
Range("D11:F11").Select
Sheets("Settings").Select
End Sub

hope this makes some sense :)

Reply to this Message

Dave Peterson - 29 Aug 2007 01:28 GMT

I don't see any place where you tried to unprotect the worksheet.

But even if your code actually includes those lines, I still don't have a guess
why they wouldn't unprotect the sheet.

> Hi Dave,
>
[quoted text clipped - 92 lines]
>
> hope this makes some sense :)

Signature

Dave Peterson

Reply to this Message

Hanno - 28 Aug 2007 10:51 GMT

Is your workbook in Shared Mode?
If so, it's unpossible to protect/unprotect

Hanno

Reply to this Message

Rate this thread: