Pasting data into excel

[curscott]

I have created a program in excel where I have the sheet password protected
and there are certain cells that have ben left unlocked for user to enter
their necessary data.

My problem is if the user copies a peice of data from something outside
excel, say word, and pastes it into the cell, excel automatically is setting
that cell now to be locked. This now prohibits the user from going back to
edit data that they should have acces to. The funny thing is that if you do
any of the paste specials the cell remains unlocked.

Any ideas or ways to prevent excel from this?

I have code that is fired on the worksheet.Change event and I have traced
through my code and nothing I am doing is setting that value. as a matter of
fact I set a breakpoint on that event and stopped it before any code is
executed and look at the cell and it has already been set to locked. so it
looks like excel is doing this in the bcakground on it own.

 

[Jim Jackson]

When Word data is pasted into Excel the Word formatting is "translated" into
Excel formatting and, evidently, includes the "Lock" format. It is
necessary, for maintaining specific formats, to "PasteSpecial" and choose one
of the options. This allows plain text to be pasted without bringing all its
background formats with it.

 

[Student]

I am having the same problem and the users of my Excel sheets are non
technical people. is there a way that I may hard code it to disable the paste
from locking the cell
please advice
thank you very much

 

[Jim Jackson]

You could place a Command Button on the sheet with the following code. The
instructions would be to press the "Paste" button rather than pasting
manually.

Sub Button1_Click()
ActiveSheet.PasteSpecial Format:="Text", _
Link:=False, DisplayAsIcon:=False
End Sub

I will post again when I find the answer to disabling manual pasting (if
there is one).

 

[Student]

Hi thank you!
here is how i solved the lock after paste in the change event i have
identified the cells that are unlocked in a for loop and i have added a
condition to unlock a target if it's ever locked.

'Unlock Target
For Each cell In Target.Cells
If Target.Locked Then
Application.Sheets(3).Unprotect Password:="password"
Target.Locked = False
Application.Sheets(3).Protect Password:="password"
End If

 

[Jim Jackson]

Way to go! I'm glad you got it resolved. As a plus, by posting your code
you have made this available to others who are in the same boat.