excel - 请参阅 Excel Undolist 与语言无关

Question

我使用了一个 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)

我猜撤消命令在德语中的命名不同。

有没有一种方法可以独立于用户使用的语言来定义撤消列表？

Answer 1

请使用控件的 ID 号。

debug.Print Application.CommandBars("Standard").Controls("&Undo").Id
=> 128 

debug.Print Application.CommandBars("Standard").FindControl(Id:=128).caption
=> &Undo