Thanks in advance for any help given!!

I have a template spreadsheet that is formatted (ie. colors, numbe
format, borders, etc). I want to be able to protect the format whil
allowing the user to copy data into the cell....basically only allowin
"paste values".

I am pretty sure I need to use an "event change" macro, but I am no
sure how exactly to say "if someone pastes something into m
spreadsheet accept only the value (or restore all origina
formatting)".

any suggestions

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=52363

Hi Celt,

It depends how your users are copying and pasting from their own
spreadsheets and if you want "to trap" them (i.e. let them use Ctrl v
for pasting and substitute the actual Paste Special Values) or not ...
If not, a quick solution is a Command Button "Paste" to be used once
the selection Edit Copy and the destination cell are selected, it will
execute :
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False

HTH
Carim

Carim to the rescue!!!

I am not sure what method they would be suing to paste.. .could b
"Ctrl" or the actual paste command. Eitherway, I only want them to b
able to input the "value" of whatever they are pasting regardless o
the method they use. Will this bit of coding accomplish that?

I had originally started writing this long event macro to identify an
text or numbers pasted into the sheet and then reapply the origina
formatting.

I just thought there had to be a simpler way

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=52363

Celt,

From a very practical standpoint :
1. If users are copying from their "reference worksheets" into your
"central spreadsheet", they could be given the instruction to Copy from
their source, and once they go to the destination worksheet and to the
destination cell ... Press "PASTE"
2. "PASTE" is a simple command button which if clicked executes
macro1()
3. Macro1 is
Sub Macro1()
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

HTH
Carim

Not a bad idea at all Carim.

However, the endusers of this template may or may not follow m
instructions (if I was a gambling man, I would put my money on the "ma
nots"). I was hoping to be able to either limit any "paste" process t
result in only paste values or to some how have excel reverse th
formatting portion of any paste procedure to the target cells origina
format.

I want my cake and to be able to eat it too! :

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=52363

Celt,

If you want, you can go a bit further ...
1. In addition to the "Paste" Button, you can under Macro Name Options
assign Control V to your macro which will produce the following : your
users will go with control c - control v with the impression to perform
a standard copy paste whereas thanks to your macro they will in reality
perform a control copy control pastespecial values ...
2. If you are really afraid of your users, you could ultimately lock in
the main menu the paste, to be 100 % on the safe side ...

HTH
Carim