How to run macro on Excel 2007 protected sheet?

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

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?

Valter wrote:

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

--

Dave Peterson

On 27 aug, 23:50, Valter wrote:
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

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

Hanno

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

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

Valter wrote:

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

--

Dave Peterson

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.

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.



Valter wrote:

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.

--

Dave Peterson

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

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.



Valter wrote:

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

--

Dave Peterson