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

C

Celt

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
 
C

Carim

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
 
C

Celt

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
 
C

Carim

Celt,
From a very practical standpoint :
Click to expand...
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
 
C

Celt

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! :
 
C

Carim

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
 
C

Carim

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
 
C

Carim

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
 
C

Celt

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!
 
C

Carim

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
 
C

Carim

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
 
C

Celt

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
 
C

Carim

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
 
C

Celt

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
 
C

Carim

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
 
C

Celt

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
 
C

Carim

Hi Celt,

Is this email address :
(e-mail address removed)
OK to send you a test worksheet ?

Carim
 
C

Celt

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change paste to only paste values 1
Paste Special Macro 2
Filling a macro button with colour ( color) 4
Formatting cells from a value in a Listbox 2
Need help with simple macros for double checking manual plugs 4
VBA 2003 cut/copy | paste values only 4
Update this macro to make it generate a counter as text 7
Copy and paste ONLY conditional format 1

Top