Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 25th, 2006, 12:18 PM
Authorized User
 
Join Date: Dec 2005
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default restrict spreadsheet to paste values only

is there a way to restrict a workbook to paste-values only?
i have a workbook set up as a form where customers input data and i don't want them to be able to paste formatting into the cells only values.

 
Old July 26th, 2006, 02:28 AM
Registered User
 
Join Date: Jul 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is an example that solve your problem (example to copy value only from sheet S1 to sheet S2):

Sheets("S1").Select
Cells.Select
Selection.Copy
Sheets("S2").Select
Selection.PasteSpecial Paste:=xlPasteValues

Ciao

Mario
 
Old August 10th, 2006, 03:06 PM
Authorized User
 
Join Date: Dec 2005
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

actually that doesn't help me. you just gave me code that performs a canned copy/paste function. what i need to do is restrict the user's functionality. i want to code the sheet so that it won't allow the user to paste formatting (i.e so that anytime the user does a paste, it automatically only pastes values). any suggestions?

 
Old August 14th, 2006, 04:34 AM
Authorized User
 
Join Date: Mar 2006
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok.. I think this will cover what you are looking for.. first to disable Ctrl-V the simplest way is to write a macro with Ctrl-V as the shortcut. so record a new macro and call it PasteValues and add v as the shortcut key, press ok, then stop recording. go to macro editor Alt-F11 and add following code

Code:
Sub PasteValues()

' Keyboard Shortcut: Ctrl+v

    On Error Resume Next

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

Sub Auto_Open()


    Application.CommandBars("Cell").Reset
        With Application.CommandBars("Cell")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click cell paste to use Pastevalues macro
            .Controls(4).Delete ' removes paste secial option
        End With

    Application.CommandBars("Row").Reset
        With Application.CommandBars("Row")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click row paste to use Pastevalues macro
            .Controls(4).Delete ' removes paste secial option
        End With

    Application.CommandBars("Column").Reset
        With Application.CommandBars("Column")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click column paste to use Pastevalues macro
            .Controls(4).Delete ' removes paste secial option
        End With

    Application.CommandBars("Worksheet Menu Bar").Reset
    With Application.CommandBars("Worksheet Menu Bar").Controls
        .Item(2).Controls(6).OnAction = "PasteValues"  ' changes edit/paste to use Pastevalues macro
        .Item(2).Controls(7).Enabled = False ' disable paste special
    End With

    Application.CommandBars("Standard").Reset
    With Application.CommandBars("Standard").Controls.Item(12)
        .Enabled = False
    End With
End Sub
any questions let me know

 
Old December 12th, 2008, 05:30 PM
Registered User
 
Join Date: Dec 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default restrict spreadsheet to paste values only

I used this VBA code in one of my workbooks.

It works fine, but when the workbook containing this code is closed and data is pasted to any other spreadsheet using the right-click mouse paste shortcut, excel is looking for the file where this code is stored.

This does not happen with keyboard shorcuts ctrl-v / ctrl-c

How this prevent this from happening?

Any help would be greatly appreciated!
 
Old April 12th, 2009, 01:10 AM
Registered User
 
Join Date: Apr 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default A simple workaround

Hello folks,

I had the same issue with a patient information spreadsheet I created. I created a simple workaroud by protecting the sheet with editing permissions on the ranges I specified. Within the ranges where editing is permitted only the values can be changed, and therefore once the sheet is locked, only the values can be copy and pasted. It's worth noting that the "cut" function is disabled in a sheet that is protected as such, but that's just one extra step of deleting the original cell contents once the copy and paste is completed.

best of luck
 
Old June 30th, 2011, 12:43 AM
Registered User
 
Join Date: Jun 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

I used the macro you provided above but now all of my spreadsheets don't have a paste special option and the paste option via mouse give an error (400).

Is there any way to reverse the changes this macro makes? I have deleted the macro and tried re-installing excel.

Thanks so much.
 
Old June 30th, 2011, 12:44 AM
Registered User
 
Join Date: Jun 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

did you find a solution?
 
Old November 8th, 2011, 07:40 AM
Registered User
 
Join Date: Nov 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'd be interested to know the solution too, as I very stupidly applied the macros to one of my workbooks before reading to the end of the thread and now I cant use right click and paste and any workbook.

Thanks
 
Old November 8th, 2011, 07:20 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

As far as Ctrl+v, I'd change the shortcut key so that it no longer over-rides the default behavior and as far as the rest of the code, I'd try

Code:
Application.CommandBars("Cell").Reset
only on each command bar item originally changed and see if that resets it. (Not gonna test it as I don't want to be stuck with the same problem.) If reinstalling Excel didn't solve the problem, I'm guessing that you'll have to deal with some Registry entries. Type "Regedit" at a command line to get into the registry editor, but be careful in the registry editor, you can wreak havoc if you don't know what you're doing. Set a system restore point before you make changes to the registry. The other thing to check is Users > YourUserName > AppData, not sure that's the correct path, but once you get to that folder, check around for an Excel folder, then take it from there.

As far as a solution to your original problem, I'd need some more info, but two things you can try is 1, put your macro into the Personal Macros workbook, which then will be available whenever Excel is open, or 2, in the workbook you have your macro in, use the Workbook_Close event to cancel it being closed unless all other workbooks are closed. Something like this:

Code:
Sub Workbook_Close(Cancel as Boolean)

If Workbooks.Count > 1 Then
  MsgBox "You must close all other workbooks before you can close this one."
  Cancel = True
End If

End Sub
Hope that helps

Last edited by mtranchi; November 8th, 2011 at 07:31 PM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Disable Copy Paste sridevi HTML Code Clinic 5 August 11th, 2007 05:09 AM
copy paste !!!!!!! dpkbahuguna Beginning VB 6 1 March 30th, 2007 10:06 AM
Copy, Paste dpkbahuguna Beginning VB 6 1 October 26th, 2006 10:30 AM
restrict to paste values only jcellens Excel VBA 1 July 18th, 2006 11:36 AM
Restrict elements guozhang XML 0 April 28th, 2004 02:26 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.