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

[Celt]

Thanks in advance for any help given!!

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

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

any suggestions?

[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

[Celt]

Carim to the rescue!!!

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

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

I just thought there had to be a simpler way.

[Carim]

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

[Celt]

Not a bad idea at all Carim.

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

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

[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

[Celt]

Carim,

How about this approach?

http://www.excelforum.com/showthread...cell+format%3F

Do you think this might be feasible for my dilemma? Once a cell is selected, I could copy the format to a hiden cell and then have some 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!!

[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

[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

[Celt]

Carim,

I think that does help.

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

Sorry to keep throwing you all these curves!!

[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

[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

[Celt]

Hi Carim,

I understand what you are asking.

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

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

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

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

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

Thanks Carim!
Celt.

[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

[Celt]

Hi Carim,

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

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

Celt.

[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

[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.

[Carim]

Hi Celt,

Is this email address :
[email protected]
OK to send you a test worksheet ?

Carim

[Celt]

Hi Carim,

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

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

Thanks !!!
Celt.

[Carim]

Celt,

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

HTH

Carim

[Celt]

Hi Carim,

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

[email protected]?

Take out the nospam of course!

Thanks!

[ben]

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 ***

[Celt]

Thanks very much Benoit!!!

That really does help.

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

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

Good luck!
Celt.

[ben]

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 ***

[rahul_ind]

Wow....excellent Ben, Celt and Carim.....this is beautiful thread. It did help me a lot...thank you all....


I wonder why it was not followed up....is it possible to change other paste menu's...i wonder was it ever completed...

Best regards,
Rahul

[rahul_ind]

Just stumbled upon another thread in different forum.


Seems this simple code in Thisworkbook will do everything to preserve format without even users know what is happening behind!! It also prevents cut and paste

Please Login or Register  to view this content.

Thanks and regards,
Rahul

[rahul_ind]

Finally, i came across another thread which shows warning when users try to cut the data in formatted sheet.....i used this below code in my project....in - thisworkbook -

posting this here thinking it might benefit others....thank you all

Please Login or Register  to view this content.

Best regards,
Rahul

[Excel 4 Me]

Hi Rahul,

Thank you for posting this macro. It works well when I copy and paste from an excel spreadsheet. However, when I try to copy from WORD or a website, the data does not paste to the target cell in my spreadsheet (it's as if the data is lost before the final paste values statement in the macro). The macro does execute the same lines of code whether the source is from a spreadsheet or not.

Do you, or anyone, know what changes could be made to this macro to allow the paste to use input copied from sources other than a spreadsheet, but still retain the formatting of the target spreadsheet cell?

Thank you.

Regards,
Dale

[Glenn Kennedy]

Excel 4 Me... This thread is years old. Rahul has not logged onto the Forum since 2013.

Technically, this is a thread hijack and is against the forum rules. Please read the yellow banner (top of page) and start your own thread. that way ACTIVE members will respond...