Prevent Paste, Allow manual input?

SDwyer

New Member
Joined
Mar 28, 2008
Messages
6
Hi,

I'm creating a sheet to take info from a series of column, use concatenate to create a text string from the info, then a vlookup to find a template name from the string. But what I need to do is limit the data to only the type that is expected from the columns. If someone means 11, I need it to say '11', not 'eleven' not '11.00', not 'XI'.

I've used formatting and data verification to limit the type of data that can be input, but I'm aware it's still possible to copy and paste into the cells as that brings the formatting from the originating cell.

So, is there any way to prevent data being pasted into a cell, but still allow manual input?

Thanks in advance
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
There is code on this site to disable menu and toolbar functions.
Maybe removing the paste functions (inc ctrl v) would work.
regards, Geoff.
 
Upvote 0
You can also replace paste with paste values only, which still allows users access to a function that they most likely use all the time and protects your formatting. The code below will do that...not my code, I think I got it here. I have been using it for some time on multiple machines with multiple users and have not had any issues.

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Overrides paste with paste values only
    Dim UndoString As String
    Dim srce As Range
    On Error GoTo err_handler
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
 
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
 
        Exit Sub
 
    End If
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
 
 
    If UndoString = "Auto Fill" Then
 
        Set srce = Selection
 
        srce.Copy
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
 
    Else
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
    End If
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

One note..this can ALL be undone by the user...all they have to do is disable macros and your code will not run. There is a workaround..do a search for "Force users to enable macros".

Jason
 
Upvote 0
I am fairly new at macros and code, however, this is exactly what I am looking for. I have copied the code and tried to apply it to my project but I can't seem to make it work. I am still getting the formula, data and fill color of the copied cell and when I run the macro it just clears the last paste session. I did enable macros from the developers tab (EXCEL 2007)

Maybe I created the macro incorrectly?
 
Upvote 0
Try changing this:

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

to this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

That worked for me.

........oops......my bad............guess it depends on where you paste the code

If you paste under "ThisWorkbook", use the former. If you paste it into one of your worksheets, use the latter.
 
Last edited:
Upvote 0
The posted code should go into ThisWorkbook, not a module. If you are doing that, then I'm not sure why it does not work for you...I've used it in both 2003 and 2007 versions of Excel without any problems.

Jason
 
Upvote 0
THANKS!! As I had said before I was pretty new at code. As it was I was just using the wrong procedure to paste the darn thing! Works great! Again thanks
 
Upvote 0
Prevent paste, allow paste special msg box

I've modified the code below to try to allow a message box that will allow the user to overwrite formatting if they wish, but I'm obviously not writing it correctly. Can someone tweak it for me?

Code:
Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Overrides paste with paste values only
    Dim UndoString As String, strMsg As String
    Dim srce As Range
    On Error GoTo err_handler
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
 
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
    If MsgBox("This operation will overwrite formatting.  Do you want to overwrite formatting?", _
    vbYesNo + vbQuestion, "Overwrite formatting?") = vbNo Then GoTo PasteSpecial
 
        Exit Sub
PasteSpecial:
    End If
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
 
 
    If UndoString = "Auto Fill" Then
 
        Set srce = Selection
 
        srce.Copy
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
 
    Else
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
    End If
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Also, I need a way to override it while I'm working on it without disabling macros altogether. Maybe a third button in the box that says "Override"?

Thanks!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top