VBA Excel-保留目标单元格格式 [英] vba excel - preserve destination cell formatting
问题描述
我对vba来说还比较陌生，所以请保持温柔:)
I am relatively new to vba, so please be gentle :)
我已经查看了各种脚本，这些脚本据说可以在ctrl c/ctrl v或copy&时保留电子表格中单元格的格式.正在使用粘贴.不幸的是，我似乎无法获得符合我的意图的任何变体.我想这可能是由于许多数据正在被复制和复制的事实所致.从其他程序复制粘贴并粘贴到工作表中(因此复制并保留其来源程序的格式).我尝试使用的所有宏似乎都试图在单元格/工作表或工作簿之间进行复制时保留格式，而从另一个程序进行复制时却无法解决数据格式.
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 v或粘贴事件上应该有一种方法，可以将复制的数据存储为变量，删除其格式并仅粘贴原始值.我尝试过使用pastespecial，但是我不确定如何强制使用pastespecial(或将paste替换为pastespecial).
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).
这是一些代码示例，但它似乎对我不起作用.我不断得到:
Here is some code sample, but it doesn't seem to work for me. I keep getting:
无法运行宏"C:... Test.xlsm'！MyPaste'.此工作簿中的宏可能不可用，或者所有宏都可能被禁用了
cannot run the macro "C:...Test.xlsm'!MyPaste'. The macro may not be available in this workbook or all macros may be disabled
肯定启用了宏并将代码粘贴到[ThisWorkbook(Code)]
Macros are definitely enabled and the code is pasted into [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
推荐答案
错误消息的原因是您的代码是事件处理程序
The reason for the error message is that your code is an event handler
请参阅:
- 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
和
- 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
基本上是 Worksheet.Change事件(Excel)用户更改工作表中的单元格时触发. Excel将工作表对象对象作为 sh传递和范围对象(Excel)作为目标.然后，您的代码将使用这些对象( Ozgrid Excel VBA速成课程第4课-公用对象).
basically the Worksheet.Change Event (Excel) is fired when the user changes a cell in the worksheet. Excel passes in the Worksheet Object object as sh and the Range Object (Excel) as Target. Your code then uses these objects (Ozgrid Excel VBA Crash Course Lesson 4 - Common objects).
按照 David Zemens 的建议，您需要使用 PasteSpecial 方法 Sheet 对象.有关更多信息，请参见 MSDN libray: PasteSpecial方法[Excel 2003 VBA语言参考] .
as David Zemens has suggested, you need to use the PasteSpecial method of the Sheet object. for further info, see MSDN libray: PasteSpecial Method [Excel 2003 VBA Language Reference].
阅读完所有内容后，您就可以在下面复制粘贴我的代码了:
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
所以，bada bing bada bing，您已经拥有了工作代码，并且已阅读了一些书，应该可以帮助您更好地了解代码的作用以及如何执行.
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.
这篇关于VBA Excel-保留目标单元格格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
