Thanks for your reply.

I tried this code, but it seems like it will prompt the user to unprotect
the file. Since the user does not have tha password, thus I don't think this
will be working at the end user's end.

I just found out that whenever I copy the data content from the web site,
then the cell will be locked, even though my initial setting is unlocked.

Any idea on this? Appreciate your help.

"Rick Rothstein (MVP - VB)" wrote:

I guess you code run this code in the Workbook_Open event...

Dim WS As Worksheet
For Each WS In Worksheets
WS.Unprotect
Next

Rick


"ah" wrote in message
...
I found that when user copy the data from a locked cell and paste it to my
template, the excel will change the cell format from unlocked to locked.

Please advice: how am I going to prevent the format from being changed?
"ah" wrote:

I've wrongly posted the following in the Excel General Question just now,
thus sorry for the duplicate posts:

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

You can supply the password as an argument to the Unprotect method. Look up
Unprotect in the help files.

Rick


"ah" wrote in message
...
Thanks for your reply.

I tried this code, but it seems like it will prompt the user to unprotect
the file. Since the user does not have tha password, thus I don't think
this
will be working at the end user's end.

I just found out that whenever I copy the data content from the web site,
then the cell will be locked, even though my initial setting is unlocked.

Any idea on this? Appreciate your help.

"Rick Rothstein (MVP - VB)" wrote:

I guess you code run this code in the Workbook_Open event...

Dim WS As Worksheet
For Each WS In Worksheets
WS.Unprotect
Next

Rick


"ah" wrote in message
...
I found that when user copy the data from a locked cell and paste it to
my
template, the excel will change the cell format from unlocked to
locked.

Please advice: how am I going to prevent the format from being changed?
"ah" wrote:

I've wrongly posted the following in the Excel General Question just
now,
thus sorry for the duplicate posts:

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

Hi Rick;

Thanks. May be I should explain it clearer. I've included the following code
into my excel now, it works when I save the file. Thanks a lot!

Please advice whether we can unlock the field even before the user save the
file?

My code is as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim WS As Worksheet

For Each WS In Worksheets

WS.Unprotect Password:="BATCH"

Range("C10:G12").Locked = False

Range("A15:K2000").Locked = False
Next


ThisWorkbook.Worksheets("Move Position to New Org Unit").Protect
Password:="BATCH"
ActiveSheet.Protect


End Sub



"Rick Rothstein (MVP - VB)" wrote:

You can supply the password as an argument to the Unprotect method. Look up
Unprotect in the help files.

Rick


"ah" wrote in message
...
Thanks for your reply.

I tried this code, but it seems like it will prompt the user to unprotect
the file. Since the user does not have tha password, thus I don't think
this
will be working at the end user's end.

I just found out that whenever I copy the data content from the web site,
then the cell will be locked, even though my initial setting is unlocked.

Any idea on this? Appreciate your help.

"Rick Rothstein (MVP - VB)" wrote:

I guess you code run this code in the Workbook_Open event...

Dim WS As Worksheet
For Each WS In Worksheets
WS.Unprotect
Next

Rick


"ah" wrote in message
...
I found that when user copy the data from a locked cell and paste it to
my
template, the excel will change the cell format from unlocked to
locked.

Please advice: how am I going to prevent the format from being changed?
"ah" wrote:

I've wrongly posted the following in the Excel General Question just
now,
thus sorry for the duplicate posts:

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

I haven't followed your thread, but I bet you want to qualify those ranges!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim WS As Worksheet

For Each WS In Worksheets
WS.Unprotect Password:="BATCH"
WS.Range("C10:G12").Locked = False
Ws.Range("A15:K2000").Locked = False
'and did you want this?
WS.protect Password:="BATCH"
Next WS

'already protected by the loop if you added that last .protect line
me.Worksheets("Move Position to New Org Unit").Protect Password:="BATCH"

End Sub

ah wrote:

Hi Rick;

Thanks. May be I should explain it clearer. I've included the following code
into my excel now, it works when I save the file. Thanks a lot!

Please advice whether we can unlock the field even before the user save the
file?

My code is as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim WS As Worksheet

For Each WS In Worksheets

WS.Unprotect Password:="BATCH"

Range("C10:G12").Locked = False

Range("A15:K2000").Locked = False
Next

ThisWorkbook.Worksheets("Move Position to New Org Unit").Protect
Password:="BATCH"
ActiveSheet.Protect


End Sub

"Rick Rothstein (MVP - VB)" wrote:

You can supply the password as an argument to the Unprotect method. Look up
Unprotect in the help files.

Rick


"ah" wrote in message
...
Thanks for your reply.

I tried this code, but it seems like it will prompt the user to unprotect
the file. Since the user does not have tha password, thus I don't think
this
will be working at the end user's end.

I just found out that whenever I copy the data content from the web site,
then the cell will be locked, even though my initial setting is unlocked.

Any idea on this? Appreciate your help.

"Rick Rothstein (MVP - VB)" wrote:

I guess you code run this code in the Workbook_Open event...

Dim WS As Worksheet
For Each WS In Worksheets
WS.Unprotect
Next

Rick


"ah" wrote in message
...
I found that when user copy the data from a locked cell and paste it to
my
template, the excel will change the cell format from unlocked to
locked.

Please advice: how am I going to prevent the format from being changed?
"ah" wrote:

I've wrongly posted the following in the Excel General Question just
now,
thus sorry for the duplicate posts:

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






--

Dave Peterson