Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
protection and changing formulas
Can i protect a sheet so that users of a spreadsheet can't manually change
the formulas, but a macro can automatically? i have a validated list that changes certain formulas based on what option is selected. I want to protect the sheet (apart from certain input cells - but i know how to do that) so that numbers will be automatically calculated, no matter what is selected - right now i get 'run-time error 1004' - and the debugger stops the macro at the 1st line where a formual is changed. so - macro can change formulas, but not humans. thats the goal. any help? thanks!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
protection and changing formulas
You can protect the worksheet in code and allow your macro to do lots of things
that the user can't. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True End With End Sub This may not work with every change your macro wants to do, though. But if that's the case, you could unprotect the sheet do the work reprotect the sheet And that should work no matter what you want to do. Derrick wrote: Can i protect a sheet so that users of a spreadsheet can't manually change the formulas, but a macro can automatically? i have a validated list that changes certain formulas based on what option is selected. I want to protect the sheet (apart from certain input cells - but i know how to do that) so that numbers will be automatically calculated, no matter what is selected - right now i get 'run-time error 1004' - and the debugger stops the macro at the 1st line where a formual is changed. so - macro can change formulas, but not humans. thats the goal. any help? thanks!! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
protection and changing formulas
So,
this coding will let me do this? Just making sure my problem is understood, so here goes: a template, for 'incompetent'(lets say) users, who only know how to insert dimensions into specific highlighted dimension cells. so A3 = 2 (width) A4 = 3 (height) A5 = 1(modular width), A6 = 2.5(Modular height) in my macro: if B2 = "Modular height template" formula in B3 = (Modular height * Width) if b2 = "Non-Modular template" b3 = Width*Height so - user only puts in numbers. the formulas change, and output different numbers when the user selects a different 'b2' option currently, i cannot do that. If i lock the B3 cells, so that the chosen formula won't be lost, it wont let me change it to another one (as per an option change in b2) with the macro. OH! another thing.. one option inserts a formula to calculate the number of vertical members, and leaves horizontal members blank the 2nd option does the opposite - leaves vertical members blank, and has a formula for horizontals. can i set up protection for the cells with the formulas only? ie. turn on/off the locked cell property for specifically chosen cells so that the end user can insert the number of vertical/horizontal members into the cells that are left blank, but not change the number of members in the cells with a formula. wow. that got more complicated. sorry! hopefully you have an idea? "Dave Peterson" wrote: You can protect the worksheet in code and allow your macro to do lots of things that the user can't. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True End With End Sub This may not work with every change your macro wants to do, though. But if that's the case, you could unprotect the sheet do the work reprotect the sheet And that should work no matter what you want to do. Derrick wrote: Can i protect a sheet so that users of a spreadsheet can't manually change the formulas, but a macro can automatically? i have a validated list that changes certain formulas based on what option is selected. I want to protect the sheet (apart from certain input cells - but i know how to do that) so that numbers will be automatically calculated, no matter what is selected - right now i get 'run-time error 1004' - and the debugger stops the macro at the 1st line where a formual is changed. so - macro can change formulas, but not humans. thats the goal. any help? thanks!! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
protection and changing formulas
If you can think of the rules, then you should be able to make a macro that does
the work. But my previous response was just how to handle the worksheet protection. And I don't see anything in your follow-up that would change that inital response. On the other hand, if you've limited the number of options for that B2 cell, you may not need a macro: =if(b2="Option 1",Option1formulahere,if(b2="option2",Option2formu lahere,.... But this kind of nesting won't support too many options (in xl2003 and below). Derrick wrote: So, this coding will let me do this? Just making sure my problem is understood, so here goes: a template, for 'incompetent'(lets say) users, who only know how to insert dimensions into specific highlighted dimension cells. so A3 = 2 (width) A4 = 3 (height) A5 = 1(modular width), A6 = 2.5(Modular height) in my macro: if B2 = "Modular height template" formula in B3 = (Modular height * Width) if b2 = "Non-Modular template" b3 = Width*Height so - user only puts in numbers. the formulas change, and output different numbers when the user selects a different 'b2' option currently, i cannot do that. If i lock the B3 cells, so that the chosen formula won't be lost, it wont let me change it to another one (as per an option change in b2) with the macro. OH! another thing.. one option inserts a formula to calculate the number of vertical members, and leaves horizontal members blank the 2nd option does the opposite - leaves vertical members blank, and has a formula for horizontals. can i set up protection for the cells with the formulas only? ie. turn on/off the locked cell property for specifically chosen cells so that the end user can insert the number of vertical/horizontal members into the cells that are left blank, but not change the number of members in the cells with a formula. wow. that got more complicated. sorry! hopefully you have an idea? "Dave Peterson" wrote: You can protect the worksheet in code and allow your macro to do lots of things that the user can't. Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True End With End Sub This may not work with every change your macro wants to do, though. But if that's the case, you could unprotect the sheet do the work reprotect the sheet And that should work no matter what you want to do. Derrick wrote: Can i protect a sheet so that users of a spreadsheet can't manually change the formulas, but a macro can automatically? i have a validated list that changes certain formulas based on what option is selected. I want to protect the sheet (apart from certain input cells - but i know how to do that) so that numbers will be automatically calculated, no matter what is selected - right now i get 'run-time error 1004' - and the debugger stops the macro at the 1st line where a formual is changed. so - macro can change formulas, but not humans. thats the goal. any help? thanks!! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell protection and changing links | Excel Discussion (Misc queries) | |||
Changing Colours With Protection | Excel Discussion (Misc queries) | |||
protection, formulas and sharing with dummies | Excel Discussion (Misc queries) | |||
Protection for formulas | Excel Worksheet Functions | |||
Changing password protection on sheets | Excel Discussion (Misc queries) |