我使用过一个Excel宏,它可以将所有数据自动粘贴为值,方法是撤销粘贴然后将其作为值粘贴。
以下是代码:
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
我正在使用英文版的Excel,它可以正常工作,然而当其他用户,比如说他们使用德文版的Excel,粘贴一些内容时,他们会在此行收到一个过程错误:
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
我猜在德语中Undo命令的名称不同。
是否有一种方法可以定义与用户使用的语言无关的撤销列表?
UndoList = Application.CommandBars("Standard").Controls(128).List(1)
和UndoList = Application.CommandBars("Standard").Controls(ID:=128).List(1)
,但没有成功。同时,使用undoname = Application.CommandBars("Standard").FindControl(Id:=128).caption
定义一个字符串,然后使用UndoList = Application.CommandBars("Standard").Controls(undoname).List(1)
也没有起作用。我做错了什么? - MustikaApplication.CommandBars("标准").FindControl(ID:=128).List(1)
- A. WebbApplication.CommandBars("Standard").FindControl(ID:=128).ListCount > 0
。 - A. Webb