Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP NEEDED
Hi;
Can someone help me urgently? I'm getting a lot of complaint from end user stating that they are not be able to change the cell contents. This is how it works: a) Staff A will fill in the template and forward it to their supervisor for approval b) The supervisor might need to make some amendments when they found that there are some mistakes with the form. c) The supervisor claim that they are not be able to change the contents in some of the cell I performed a check and found that the cell properties has been changed to "Locked" when it reaches the supervisor. For your information, I did not lock the cell. In the event if it is locked, then the staff will not be able to fill in the cell at the 1st place, as they don't have the password to unprotect it. I suspect the staff A is copying the data from another source and paste it into the template when they performed the data entry. Please advice: How am i going to prevent the user from changing the cell properties from "unlocked" to "Locked" even when they are copying the data from another source? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP NEEDED
My suggestion:
Write in a quick event macro that unlocks the specified range(s) for before the workbook closes. Something like the following: Private Sub Workbook_BeforeClose(Cancel As Boolean) Range("B4:D12").Unprotect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub This would go in the ThisWorkbook section of your VBE. Hope this helps! -- -SA "ah" wrote: Hi; Can someone help me urgently? I'm getting a lot of complaint from end user stating that they are not be able to change the cell contents. This is how it works: a) Staff A will fill in the template and forward it to their supervisor for approval b) The supervisor might need to make some amendments when they found that there are some mistakes with the form. c) The supervisor claim that they are not be able to change the contents in some of the cell I performed a check and found that the cell properties has been changed to "Locked" when it reaches the supervisor. For your information, I did not lock the cell. In the event if it is locked, then the staff will not be able to fill in the cell at the 1st place, as they don't have the password to unprotect it. I suspect the staff A is copying the data from another source and paste it into the template when they performed the data entry. Please advice: How am i going to prevent the user from changing the cell properties from "unlocked" to "Locked" even when they are copying the data from another source? Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP NEEDED
Hi;
Thanks for your reply. I tried to insert the code that you provided, however I get the following error when I close the file: Run-time error '438' Object doesn't support this property or method and the following code is being highlighted: Range("D4:E6").Unprotect DrawingObjects:=True, Contents:=True, Scenarios:=True Please advice. Thanks in advance "StumpedAgain" wrote: My suggestion: Write in a quick event macro that unlocks the specified range(s) for before the workbook closes. Something like the following: Private Sub Workbook_BeforeClose(Cancel As Boolean) Range("B4:D12").Unprotect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub This would go in the ThisWorkbook section of your VBE. Hope this helps! -- -SA "ah" wrote: Hi; Can someone help me urgently? I'm getting a lot of complaint from end user stating that they are not be able to change the cell contents. This is how it works: a) Staff A will fill in the template and forward it to their supervisor for approval b) The supervisor might need to make some amendments when they found that there are some mistakes with the form. c) The supervisor claim that they are not be able to change the contents in some of the cell I performed a check and found that the cell properties has been changed to "Locked" when it reaches the supervisor. For your information, I did not lock the cell. In the event if it is locked, then the staff will not be able to fill in the cell at the 1st place, as they don't have the password to unprotect it. I suspect the staff A is copying the data from another source and paste it into the template when they performed the data entry. Please advice: How am i going to prevent the user from changing the cell properties from "unlocked" to "Locked" even when they are copying the data from another source? Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP NEEDED
Sorry about that... not sure what I was thinking.... try the following or
just use the tool Tools-Protection-Allow Users to Edit Ranges... Sub Macro2() ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range( _ "A3:C9") ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub -- -SA "ah" wrote: Hi; Thanks for your reply. I tried to insert the code that you provided, however I get the following error when I close the file: Run-time error '438' Object doesn't support this property or method and the following code is being highlighted: Range("D4:E6").Unprotect DrawingObjects:=True, Contents:=True, Scenarios:=True Please advice. Thanks in advance "StumpedAgain" wrote: My suggestion: Write in a quick event macro that unlocks the specified range(s) for before the workbook closes. Something like the following: Private Sub Workbook_BeforeClose(Cancel As Boolean) Range("B4:D12").Unprotect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub This would go in the ThisWorkbook section of your VBE. Hope this helps! -- -SA "ah" wrote: Hi; Can someone help me urgently? I'm getting a lot of complaint from end user stating that they are not be able to change the cell contents. This is how it works: a) Staff A will fill in the template and forward it to their supervisor for approval b) The supervisor might need to make some amendments when they found that there are some mistakes with the form. c) The supervisor claim that they are not be able to change the contents in some of the cell I performed a check and found that the cell properties has been changed to "Locked" when it reaches the supervisor. For your information, I did not lock the cell. In the event if it is locked, then the staff will not be able to fill in the cell at the 1st place, as they don't have the password to unprotect it. I suspect the staff A is copying the data from another source and paste it into the template when they performed the data entry. Please advice: How am i going to prevent the user from changing the cell properties from "unlocked" to "Locked" even when they are copying the data from another source? Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP NEEDED
Hi;
Thanks for your reply. I found out that whenever the user copy the data from the web page, then the excel will change the cell into "Locked" automatically. Do you have any idea on how to prevent the cell's property from being changed from "unlocked" to "locked"? "StumpedAgain" wrote: Sorry about that... not sure what I was thinking.... try the following or just use the tool Tools-Protection-Allow Users to Edit Ranges... Sub Macro2() ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range( _ "A3:C9") ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub -- -SA "ah" wrote: Hi; Thanks for your reply. I tried to insert the code that you provided, however I get the following error when I close the file: Run-time error '438' Object doesn't support this property or method and the following code is being highlighted: Range("D4:E6").Unprotect DrawingObjects:=True, Contents:=True, Scenarios:=True Please advice. Thanks in advance "StumpedAgain" wrote: My suggestion: Write in a quick event macro that unlocks the specified range(s) for before the workbook closes. Something like the following: Private Sub Workbook_BeforeClose(Cancel As Boolean) Range("B4:D12").Unprotect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub This would go in the ThisWorkbook section of your VBE. Hope this helps! -- -SA "ah" wrote: Hi; Can someone help me urgently? I'm getting a lot of complaint from end user stating that they are not be able to change the cell contents. This is how it works: a) Staff A will fill in the template and forward it to their supervisor for approval b) The supervisor might need to make some amendments when they found that there are some mistakes with the form. c) The supervisor claim that they are not be able to change the contents in some of the cell I performed a check and found that the cell properties has been changed to "Locked" when it reaches the supervisor. For your information, I did not lock the cell. In the event if it is locked, then the staff will not be able to fill in the cell at the 1st place, as they don't have the password to unprotect it. I suspect the staff A is copying the data from another source and paste it into the template when they performed the data entry. Please advice: How am i going to prevent the user from changing the cell properties from "unlocked" to "Locked" even when they are copying the data from another source? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent help needed | Excel Worksheet Functions | |||
Urgent Help Needed please | Excel Worksheet Functions | |||
Help Needed Urgent | Excel Discussion (Misc queries) | |||
Urgent help needed | Excel Worksheet Functions | |||
Urgent help needed! | Excel Worksheet Functions |