Macro to only allow pasting of values and not format??
 

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

Macro to only allow pasting of values and not format??
 

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

Macro to only allow pasting of values and not format??
 

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

Macro to only allow pasting of values and not format??
 

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

Macro to only allow pasting of values and not format??
 

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

Macro to only allow pasting of values and not format??
 

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

Macro to only allow pasting of values and not format??
 

Carim,

How about this approach?

http://www.excelforum.com/showthread...ll+forma t%3F

Do you think this might be feasible for my dilemma? Once a cell i
selected, I could copy the format to a hiden cell and then have som
sort of event macro that would compare the two if it was changed?

How would I approach coding soething like this?

As always, thanks for your guidance and incredible patience!

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

Macro to only allow pasting of values and not format??
 

Celt,

There are always many different paths to a similar solution ...
Let me take a look at this approach ...
and I will get back to you asap ...

Carim

Macro to only allow pasting of values and not format??
 

Celt,

A trick similar to an individual hidden cell would be a hidden sheet
would be nothing but a replicate of your working sheet .
After all users have made their input, you could run a simple macro
which would copy all the formats from the hidden sheet back to the
working sheet ...
something along these lines :
Sub Macro1()
Sheets("Sheet2").Select
Cells.Select
Selection.Copy
Sheets("Sheet1").Select
Cells.Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

Does it help ???
Carim

Macro to only allow pasting of values and not format??
 

Carim,

I think that does help.

That would work for all my sheets except one (I think). One of m
sheets allows the user to insert rows. If the hidden sheet and th
"real" sheet aren't exactly the same, barring input, wouldn't tha
potentially make the formatting look strange?

Sorry to keep throwing you all these curves!

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

Macro to only allow pasting of values and not format??
 

Celt,

I do not know how many sheets you are dealing with ...
But you are right, the hidden sheet and the "real" sheet should be
identical ...
Now, if there is only a single sheet where users can insert rows, you
could have the hidden sheet adjusting itself in the background ...
It things get too complicated to handle, it could be easier to go back
to the original idea of temporarily preventing users from having the
paste choice in the Edit menu ...
It is your choice...

Carim

Macro to only allow pasting of values and not format??
 

Hello Celt,

As I am playing around with your problem, I just realized there is an
important question I forgot to ask you ...
When you are referring to "preserving your Format in your template",
are you talking about one single Format pattern applied to all your
cells, or is it that each and every cell has its own particular Format
....?
A Format pattern could be by rows, by columns or by whatever identifier
.... What I mean is that if there is a Format structure, there is an
underlying logic ... and this logic,once identified, can be programmed
....
I hope my question is clear enough ...
Cheers
Carim

Macro to only allow pasting of values and not format??
 

Hi Carim,

I understand what you are asking.

The workbook has roughly ten sheets in it. Each sheet has multipl
formats on it. Some are formatted for aphla characters, some fo
numeric and some for commentary (ie, merged cells, wrapped tex
etc...). Each sheet is also password protected.

Not every cell is unique. I guess you could say they are groupe
together in formatting styles. For example, certain cells in rang
A6:C20 are formatted for aplha codes, text. Range D6:G20 has certai
cells formatted for numeric entry. Finally, range A22:E31 is formatte
for commentary.

Due to the multiple formats, I was thinking the answer offered in th
other post might work. When a user selects a cell, the format i
automatically copied somewhere, the user makes their change, then th
macro comapres the new format to the copied original, if they don'
agree, the original format is copied back. I suppose copying th
format of the whole row could work too.

I currently have my nose stuck in a VBA book hping to get a bette
understanding of all this code.

I really appreciate your help!! I you want to see what the spreadshee
looks like let me know, I have no problem attaching it here.

Thanks Carim!
Celt

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

Macro to only allow pasting of values and not format??
 

Hi Celt,

Thanks for your comments.
But I still have a few questions :
Are your users copying data from their own worksheets into your
template ... what is the process ?
If they are copying data, is it a cell by cell process ?
Could your template be automatically filled-in by links or not ?

Carim

Macro to only allow pasting of values and not format??
 

Hi Carim,

Users can input data into the sheets either manually or by cut an
paste from their own worksheets. Since the spreadsheet is locked, it i
a cell by cell process.

I don't use any automatic links in these sheets. These users are al
on different networks, so I am not sure that is a viable option. Th
linking I have done in the past on other sheets has alwyas been a bi
"tricky" as well and needed a lot of maintenance.

Celt

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

Macro to only allow pasting of values and not format??
 

Hi Celt,

Is there a chance your users would stick to the following rule in order
to Paste :
1. Either use Ctrl V
2. or Click on Menu

Carim

Macro to only allow pasting of values and not format??
 

Hi Carim,

Yes to both. I am 100% certain that my end users will either use

1.ctrl-v
2.the menu
3.right click on the mouse

Thanks for your continued help Carim!!

Celt

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

Macro to only allow pasting of values and not format??
 

Hi Celt,

Is this email address :

OK to send you a test worksheet ?

Carim

Macro to only allow pasting of values and not format??
 

Hi Carim,

I think that is OK to use. I assume it will get forwarded to my rea
email account like these postings do.

Let me know once you have sent it. If I don't get it, I'll give yo
another address.

Thanks !!!
Celt

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

Macro to only allow pasting of values and not format??
 

Celt,

Just dropped you an email with a test worksheet ...

HTH

Carim

Macro to only allow pasting of values and not format??
 

Hi Carim,

I guess I don't know where that email goes. Can you send it to thi
email:

?

Take out the nospam of course!

Thanks

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

Macro to only allow pasting of values and not format??
 

Hello,

I have the same needs than you :

I need to protect the automatic format of Excel cells but to let users
to input values.

I don't know what is the best method. I also thought to have some
"reference" cells in a hidden sheet and to copy the format when cells
are modified but I'm using events for other purposes and I didn't find
the good way to do so.

From now I've protected from Ctrl+V commands in using events and OnKey
Method to trap the CtrlV.

1) Create "Class" with :
Public WithEvents App As Application

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

2)In "ThisWorkbook" :
Private Sub Workbook_Open()
Application.OnKey "^v", "MyCtrlV"
End Sub

Private Sub Workbook_Activate()
Application.OnKey "^v", "MyCtrlV"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^v"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^v"
End Sub

3) In a module :
Sub MyCtrlV()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

I'm now thinking to modify the menus of Excel in my application (Edit
menu and Right key click), so the Paste command will have the same
behaviour as "past special value". I'm not expert in VB but I think that
should be possible and will complete the protection.

Hope it can help you also,
Benoit

*** Sent via Developersdex http://www.developersdex.com ***

Macro to only allow pasting of values and not format??
 

Thanks very much Benoit!!!

That really does help.

If you are able to get the coding to work on the other menus, I woul
love to see how you did it.

One other thing I was thinking... you would also need to alter th
return key as users can paste using that as well, right?

Good luck!
Celt

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

Macro to only allow pasting of values and not format??
 

Yes Celt,

Even if I'm using excel for years I sometime learn so basic things !...

I've done what you said but also renamed "MyCtrlV" in "MyPaste" and
changed the macro :

Sub MyPaste()
' Will act like Copy paste Value
If Application.CutCopyMode Then
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
End Sub

For "ThisWorkbook" it is now including the following code :

Private Sub Workbook_Open()
Application.OnKey "^v", "MyPaste"
Application.OnKey "{RETURN}", "MyPaste"
End Sub

Private Sub Workbook_Activate()
Application.OnKey "^v", "MyPaste"
Application.OnKey "{RETURN}", "MyPaste"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^v"
Application.OnKey "{RETURN}"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^v"
Application.OnKey "{RETURN}"
End Sub

For the menus I don't know when I'll have some time to work on it. If
someone already have the solution on hand...

Benoit

*** Sent via Developersdex http://www.developersdex.com ***