vba excel - 保留目标单元格格式

vba excel - preserve destination cell formatting

提问by user2292941

I am relatively new to vba, so please be gentle :)

我对 vba 比较陌生，所以请保持温和:)

I have reviewed various scripts which supposedly preserve the formatting of cells on a spreadsheet when ctrl c/ctrl v or copy & paste is being used. Unfortunately I cannot seem to get any of the variations to work for my intentions. I suppose this might be due to the fact that a lot of the data being copy & pasted is being copied from other programs and pasted into the worksheet(therefore copying and keeping the formatting of the program from which it came). All of the macros I've tried to work with all seem to attempt to preserve formatting when copying between cells/worksheets or workbooks and doesn't address the data format when copying from another program.

我已经查看了各种脚本，这些脚本在使用 ctrl c/ctrl v 或复制和粘贴时，据称会保留电子表格上单元格的格式。不幸的是，我似乎无法让任何变化符合我的意图。我想这可能是因为很多被复制和粘贴的数据是从其他程序复制并粘贴到工作表中的（因此复制并保留它来自的程序的格式）。我尝试使用的所有宏似乎都试图在单元格/工作表或工作簿之间复制时保留格式，并且在从另一个程序复制时没有解决数据格式。

I'm looking for an alternate approach. From a logical standpoint, I'm thinking that there should be a way on ctrl v or paste event, to have the copied data stored as a variable, stripped of its formatting and to only paste the raw value. I've tried playing around with pastespecial, but I'm not sure how to force a pastespecial (or replace paste with pastespecial).

我正在寻找替代方法。从逻辑的角度来看，我认为在 ctrl v 或 paste 事件上应该有一种方法，将复制的数据存储为变量，去除其格式并仅粘贴原始值。我试过使用pastespecial，但我不确定如何强制使用pastespecial（或用pastespecial 替换paste）。

Here is some code sample, but it doesn't seem to work for me. I keep getting:

这是一些代码示例，但它似乎对我不起作用。我不断得到：

cannot run the macro "C:...Test.xlsm'!MyPaste'. The macro may not be available in this workbook or all macros may be disabled

无法运行宏“C:...Test.xlsm'!MyPaste'。此工作簿中可能没有该宏，或者可能禁用了所有宏

Macros are definitely enabled and the code is pasted into [ThisWorkbook(Code)]

宏肯定是启用的，代码粘贴到[ThisWorkbook(Code)]

Option Explicit

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

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error GoTo Whoa

    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then GoTo LetsContinue

    '~~> Undo the paste that the user did but we are not clearing the clipboard
    '~~> so the copied data is still in memory
    Application.Undo

    If UndoList = "Auto Fill" Then Selection.Copy

    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    On Error GoTo 0

    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select

LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

回答by Our Man in Bananas

The reason for the error message is that your code is an event handler

错误信息的原因是你的代码是一个事件处理程序

see:

看：

• MSDN Office Online Reference: Creating VBA Macros to Manipulate Worksheets in Excel 2007
• MSDN Office Online Reference: Running VBA Code When Events Occur in Excel 2010

• MSDN Office Online 参考：在 Excel 2007 中创建 VBA 宏来操作工作表
• MSDN Office Online 参考：Excel 2010 中发生事件时运行 VBA 代码

and

和

• Chip Pearson MVP and all round Excel Chieftan: Events And Event Procedures In VBA
• Ozgrid MVP and all round Excel VBA Top Banana: Worksheet Events In Excel VBA

• Chip Pearson MVP 和全能 Excel Chieftan：VBA 中的事件和事件程序
• Ozgrid MVP 和全方位 Excel VBA 顶级香蕉：Excel VBA 中的工作表事件

basically the Worksheet.Change Event (Excel)is fired when the user changes a cell in the worksheet. Excel passes in the Worksheet Objectobject as shand the Range Object (Excel)as Target. Your code then uses these objects(Ozgrid Excel VBA Crash Course Lesson 4- Common objects).

基本上，当用户更改工作表中的单元格时，会触发Worksheet.Change 事件 (Excel)。Excel中通过在工作表对象对象作为SH和范围对象（Excel）中作为目标。然后您的代码使用这些对象（Ozgrid Excel VBA 速成课程第 4 课-通用对象）。

as David Zemenshas suggested, you need to use the PasteSpecialmethod of the Sheetobject. for further info, see MSDN libray: PasteSpecial Method [Excel 2003 VBA Language Reference].

正如David Zemens所建议的，您需要使用Sheet对象的PasteSpecial方法。有关详细信息，请参阅MSDN 库：PasteSpecial 方法 [Excel 2003 VBA 语言参考]。

and when you have finished all that reading, you will be ready to copy paste my code below:

当您完成所有阅读后，您就可以复制粘贴我的代码如下：

Option Explicit

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

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error GoTo Whoa

    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then GoTo LetsContinue

    '~~> Undo the paste that the user did but we are not clearing the clipboard
    '~~> so the copied data is still in memory
    Application.Undo

    If UndoList = "Auto Fill" Then Selection.Copy

    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.PasteSpecial Paste:=xlPasteValues
    On Error GoTo 0

    '~~> Retain selection of the pasted data
    Target.Select

LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

so, bada bing bada bing, you have your working code, and some reading that should help you to understand better what your code is doing and how it does it.

所以，bada bing bada bing你有你的工作代码，还有一些阅读，应该可以帮助你更好地理解你的代码在做什么以及它是如何做的。