Force Paste Special Values

[hatman]

HAH... I think i have asked more questions today than in all the time since I became a member... I guess that means I'm pushing the envelope.

Anywho. I have a workbook where I want to retain FORMATS in all of my cells in all of my sheets, but allow the user to enter data. So I got this code to undo PASTE and instead do a PASTE SPECIAL VALUES:

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

    Dim UndoString As String

    On Error GoTo err_handler

    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoString, 5) = "Paste" Then 'Only allow Paste Special|Values
            Application.ScreenUpdating = False
            Application.Undo
            Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            Application.ScreenUpdating = True
    End If

    Exit Sub

err_handler:

End Sub

Only I had a co-worker submarine this code by simply entering some data, then dragging to fill across. All my formats went south. Any way to fix that?

 

[Walky]

Oups...

 

[AOB]

This looks extremely useful but I'm having difficulty pulling the undo control list from the command bars. The error handler kicks in whenever it reaches this line :

UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)

This is what I get from the debugger :

Method 'List' of object '_CommandBarComboBox' failed
<Method failed ?_CommandBarComboBox? object of ?List?><Method failed ?_CommandBarComboBox? object of ?List?><Method failed ?_CommandBarComboBox? object of ?List?><Method failed ?_CommandBarComboBox? object of ?List?>
Is there a library I need to include or does this need to be coded differently in different versions of Excel (I'm using XL2007...)

Thanks!

AOB</Method></Method></Method></Method>

 

[Walky]

Hi,

I presume it is due to the fact that you are using Excel 2007. Since the command bars of the previous versions of Excel have been replaced by the ribbon, this is one of the things that are not "portable" to Excel 2007 and 2010.

You'll have to ask for help from some gurus in order to get a replacement script, my knowledge in that field has reached its limits

Rgds,

W.

This looks extremely useful but I'm having difficulty pulling the undo control list from the command bars. The error handler kicks in whenever it reaches this line :

UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)

This is what I get from the debugger :

Method 'List' of object '_CommandBarComboBox' failed
<Method failed ?_CommandBarComboBox? object of ?List?><Method failed ?_CommandBarComboBox? object of ?List?><Method failed ?_CommandBarComboBox? object of ?List?><Method failed ?_CommandBarComboBox? object of ?List?>
Is there a library I need to include or does this need to be coded differently in different versions of Excel (I'm using XL2007...)

Thanks!

AOB</Method></Method></Method></Method>

 

[AOB]

Thanks Walky,

Actually, I think the issue is that the sheet in question is protected, which disables the undo function for some reason. I found by unprotecting the sheet first, it picked up the control list as expected!

Problem I have now is, for some reason it still seems to be pasting formats in some fashion, rather than pure values. I have conditional formatting set up on the sheet and when the user pastes in, the code appears to only paste values and my own CI kicks in. But oddly, if/when that pasted data is subsequently deleted, the format from the original workbook (i.e. from which that data was pasted) appears. Which is quite annoying...

But thanks for reverting! I'll continue to work away at it!

AOB

 

[JonnyPeters]

This code is exactly what I've been looking for. Only one issue I can't seem to figure out-

When copying data from external sources, the undo/paste clears out the cell. For example, if I have a series of data in word or notepad and I paste it into a cell it will revert back to the data that was in the cell previously. Any ideas?

 

[msgoldak]

Thank you, hatman!! This is great.

 

[Msomaji]

Alternatively, you can replace

If UndoString = "Auto Fill" Then
                Set Srce = Selection
                Srce.SpecialCells(xlVisible).Copy
                Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False  
                Application.SendKeys "{ESC}"    
                Union(Target, Srce).Select      
                
            Else    ' PASTE
                Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If

for

                If UndoString = "Auto Fill" Then
                Set Srce = Selection           
                Set Dest = Target
                
                Srce.AutoFill Destination:=Union(Srce, Dest), Type:=xlFillValues
                Application.SendKeys "{ESC}"   
                Union(Target, Srce).Select      
                
            Else    ' PASTE
                Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If

So 'Auto fill' does the job instead of copying the same value, and also the clipboard is not longer used (no copy).

 

[vzq032372]

This works well from worksheet to worksheet but not workbook to workbook nor from HTML.
For example, we have a system that users will copy a call tracking # from to the log (worksheet) which disables the formatting on the macro heavy workbook log. Any suggestions?

 

[mark52c]

I know this is a very old post (2006!), but I thought you should know I found this code to be exactly what I was looking for! Thanks very much.

 

[Bagel_Mon]

Just wondering if I'm doing something incorrect.. I'm very inexperienced with VBA.

We simply copy the code and paste it in the 'ThisWorkbook' file within the Microsoft Excel Objects folder..? Besides saving this & the workbook as a macro-enabled Excel, what am I missing?

Thanks in advance!