Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Question
Greetings,
I have a worksheet with linking formulas in 79 cells (named ranges), ToolbarsControl ToolboxCommandButtons and a ToolbarsFormsComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Question
If you go to the Properties for the button (or combobox or individual cells
or whatever else doesn't need to be locked), you'll probably see that the 'Locked' field is checked -- that's the default. Simply uncheck it and relock your sheet and you should be fine. "Minitman" wrote: Greetings, I have a worksheet with linking formulas in 79 cells (named ranges), ToolbarsControl ToolboxCommandButtons and a ToolbarsFormsComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Question
Hey IT Dev Guy,
Thanks for the reply. I didn't make myself clear. I am looking for a vba solution. To simplify, The sheet has two states - Locked for viewing and unlocked for editing. A CommandButton is used to toggle between the states. Here is the code I am trying to use to unlock named ranges pfCell_1 to pfCell_100: For i = 1 To 100 Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<< Next i So far all I get is this error message: Run-time error '1004': Unable to set the locked property of the range class Debug highlighted the line in front of the '<<<< Any ideas as to what is wrong or missing in this code? Any help will be greatly appreciated. -Minitman On Thu, 8 May 2008 11:10:00 -0700, IT Dev Guy wrote: If you go to the Properties for the button (or combobox or individual cells or whatever else doesn't need to be locked), you'll probably see that the 'Locked' field is checked -- that's the default. Simply uncheck it and relock your sheet and you should be fine. "Minitman" wrote: Greetings, I have a worksheet with linking formulas in 79 cells (named ranges), ToolbarsControl ToolboxCommandButtons and a ToolbarsFormsComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Question
I think you can't change the locked properrty while the sheet is protected.
Tim "Minitman" wrote in message ... Hey IT Dev Guy, Thanks for the reply. I didn't make myself clear. I am looking for a vba solution. To simplify, The sheet has two states - Locked for viewing and unlocked for editing. A CommandButton is used to toggle between the states. Here is the code I am trying to use to unlock named ranges pfCell_1 to pfCell_100: For i = 1 To 100 Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<< Next i So far all I get is this error message: Run-time error '1004': Unable to set the locked property of the range class Debug highlighted the line in front of the '<<<< Any ideas as to what is wrong or missing in this code? Any help will be greatly appreciated. -Minitman On Thu, 8 May 2008 11:10:00 -0700, IT Dev Guy wrote: If you go to the Properties for the button (or combobox or individual cells or whatever else doesn't need to be locked), you'll probably see that the 'Locked' field is checked -- that's the default. Simply uncheck it and relock your sheet and you should be fine. "Minitman" wrote: Greetings, I have a worksheet with linking formulas in 79 cells (named ranges), ToolbarsControl ToolboxCommandButtons and a ToolbarsFormsComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Question
Hey Tim,
Thanks for the reply. Do you mean something like this: Worksheets("Sheet1").Activate ActiveSheet.Unprotect For i = 1 To 100 Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<< Next i With ActiveSheet .Protect _ .EnableSelection = xlNoSelection End With It still gives this same error message. Run-time error '1004': Unable to set the locked property of the range class Debug highlighted the line in front of the '<<<<, the same as last time. That seemed like a possibility, but when I plugged in the unprotect in front of the locking code, it made no difference. Any other ideas as to why this is erroring out -Minitman On Thu, 8 May 2008 22:01:17 -0700, "Tim Williams" <timjwilliams at gmail dot com wrote: I think you can't change the locked properrty while the sheet is protected. Tim "Minitman" wrote in message .. . Hey IT Dev Guy, Thanks for the reply. I didn't make myself clear. I am looking for a vba solution. To simplify, The sheet has two states - Locked for viewing and unlocked for editing. A CommandButton is used to toggle between the states. Here is the code I am trying to use to unlock named ranges pfCell_1 to pfCell_100: For i = 1 To 100 Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<< Next i So far all I get is this error message: Run-time error '1004': Unable to set the locked property of the range class Debug highlighted the line in front of the '<<<< Any ideas as to what is wrong or missing in this code? Any help will be greatly appreciated. -Minitman On Thu, 8 May 2008 11:10:00 -0700, IT Dev Guy wrote: If you go to the Properties for the button (or combobox or individual cells or whatever else doesn't need to be locked), you'll probably see that the 'Locked' field is checked -- that's the default. Simply uncheck it and relock your sheet and you should be fine. "Minitman" wrote: Greetings, I have a worksheet with linking formulas in 79 cells (named ranges), ToolbarsControl ToolboxCommandButtons and a ToolbarsFormsComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Question
Hey, sorry about the misunderstanding before. Tim is right about needing to
unlock the sheet first. I tried pasting your code into a macro and debugging and it got thru the lock piece fine for me. Are you positive your ranges are all defined as you're passing them? For example, if the value of "i" when you get the error is 5, maybe range pfCell_5 is missing...? If it stops at the beginning - i.e., the value is 1 - try temporarily skipping the loop and just hard-coding the Range as "pfCell_1" -- if it works that way, maybe you need to change your range from '"pfCell_" & i' to '"pfCell_" + cstr(i)' (I had to make many weird changes when I did my code, mainly due to users having different versions, from Excel97 to current -- and I don't recall if that's why I used the cstr command...) Good luck, and let us know what happens. "Minitman" wrote: Hey Tim, Thanks for the reply. Do you mean something like this: Worksheets("Sheet1").Activate ActiveSheet.Unprotect For i = 1 To 100 Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<< Next i With ActiveSheet .Protect _ .EnableSelection = xlNoSelection End With It still gives this same error message. Run-time error '1004': Unable to set the locked property of the range class Debug highlighted the line in front of the '<<<<, the same as last time. That seemed like a possibility, but when I plugged in the unprotect in front of the locking code, it made no difference. Any other ideas as to why this is erroring out -Minitman On Thu, 8 May 2008 22:01:17 -0700, "Tim Williams" <timjwilliams at gmail dot com wrote: I think you can't change the locked properrty while the sheet is protected. Tim "Minitman" wrote in message .. . Hey IT Dev Guy, Thanks for the reply. I didn't make myself clear. I am looking for a vba solution. To simplify, The sheet has two states - Locked for viewing and unlocked for editing. A CommandButton is used to toggle between the states. Here is the code I am trying to use to unlock named ranges pfCell_1 to pfCell_100: For i = 1 To 100 Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<< Next i So far all I get is this error message: Run-time error '1004': Unable to set the locked property of the range class Debug highlighted the line in front of the '<<<< Any ideas as to what is wrong or missing in this code? Any help will be greatly appreciated. -Minitman On Thu, 8 May 2008 11:10:00 -0700, IT Dev Guy wrote: If you go to the Properties for the button (or combobox or individual cells or whatever else doesn't need to be locked), you'll probably see that the 'Locked' field is checked -- that's the default. Simply uncheck it and relock your sheet and you should be fine. "Minitman" wrote: Greetings, I have a worksheet with linking formulas in 79 cells (named ranges), ToolbarsControl ToolboxCommandButtons and a ToolbarsFormsComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Question
Hey IT Dev Guy,
Thanks for the reply. I tried your suggestion with this code: Range("pfCell_4).Locked = False Which is the first named range in the For/Next loop. I just realized that I forgot to mention in the earlier post that these named ranges were also merged cells. So I had to add MergeArea like so to get it to work: Range("pfCell_4).MergeArea.Locked = False It seems that vba has to be told that a merged cell is in a merge area. It also works in the For/Next loop with the variable. Thanks again for the help. -Minitman On Fri, 9 May 2008 08:17:03 -0700, IT Dev Guy wrote: Hey, sorry about the misunderstanding before. Tim is right about needing to unlock the sheet first. I tried pasting your code into a macro and debugging and it got thru the lock piece fine for me. Are you positive your ranges are all defined as you're passing them? For example, if the value of "i" when you get the error is 5, maybe range pfCell_5 is missing...? If it stops at the beginning - i.e., the value is 1 - try temporarily skipping the loop and just hard-coding the Range as "pfCell_1" -- if it works that way, maybe you need to change your range from '"pfCell_" & i' to '"pfCell_" + cstr(i)' (I had to make many weird changes when I did my code, mainly due to users having different versions, from Excel97 to current -- and I don't recall if that's why I used the cstr command...) Good luck, and let us know what happens. "Minitman" wrote: Hey Tim, Thanks for the reply. Do you mean something like this: Worksheets("Sheet1").Activate ActiveSheet.Unprotect For i = 1 To 100 Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<< Next i With ActiveSheet .Protect _ .EnableSelection = xlNoSelection End With It still gives this same error message. Run-time error '1004': Unable to set the locked property of the range class Debug highlighted the line in front of the '<<<<, the same as last time. That seemed like a possibility, but when I plugged in the unprotect in front of the locking code, it made no difference. Any other ideas as to why this is erroring out -Minitman On Thu, 8 May 2008 22:01:17 -0700, "Tim Williams" <timjwilliams at gmail dot com wrote: I think you can't change the locked properrty while the sheet is protected. Tim "Minitman" wrote in message .. . Hey IT Dev Guy, Thanks for the reply. I didn't make myself clear. I am looking for a vba solution. To simplify, The sheet has two states - Locked for viewing and unlocked for editing. A CommandButton is used to toggle between the states. Here is the code I am trying to use to unlock named ranges pfCell_1 to pfCell_100: For i = 1 To 100 Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<< Next i So far all I get is this error message: Run-time error '1004': Unable to set the locked property of the range class Debug highlighted the line in front of the '<<<< Any ideas as to what is wrong or missing in this code? Any help will be greatly appreciated. -Minitman On Thu, 8 May 2008 11:10:00 -0700, IT Dev Guy wrote: If you go to the Properties for the button (or combobox or individual cells or whatever else doesn't need to be locked), you'll probably see that the 'Locked' field is checked -- that's the default. Simply uncheck it and relock your sheet and you should be fine. "Minitman" wrote: Greetings, I have a worksheet with linking formulas in 79 cells (named ranges), ToolbarsControl ToolboxCommandButtons and a ToolbarsFormsComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
Sheet protection question | Excel Discussion (Misc queries) | |||
Sheet protection error msg - Unrequested sheet activation | Excel Programming | |||
sheet protection question | Excel Programming |