Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 :) |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to run macro on Excel 2007 protected sheet?
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 |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to run macro on Excel 2007 protected sheet?
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 |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to run macro on Excel 2007 protected sheet?
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 |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to run macro on Excel 2007 protected sheet?
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 |
#6
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to run macro on Excel 2007 protected sheet?
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. |
#7
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to run macro on Excel 2007 protected 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 |
#8
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to run macro on Excel 2007 protected sheet?
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 :) |
#9
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to run macro on Excel 2007 protected sheet?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to enable a macro in protected sheet | Excel Discussion (Misc queries) | |||
Macro Error when Sheet is Protected | Excel Discussion (Misc queries) | |||
macro on protected sheet-error | Excel Discussion (Misc queries) | |||
use macro button to run macro in protected sheet | Excel Discussion (Misc queries) | |||
How can you create a macro on a protected sheet? | Excel Discussion (Misc queries) |