指独立于Excel Undolist语言 [英] Refer to Excel Undolist language independent

问题描述

我使用了一个Excel宏，它通过撤消粘贴并将其粘贴为值来自动将所有数据粘贴为值.

I have used an Excel macro that automatically pastes all data as values, by undoing the pasting and pasting it as value.

这是代码:

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版本粘贴了一些东西，他们在这一行中得到了一个过程错误:

I am using the English Excel version where it works fine, however when other users, who e.g. have a German Excel version paste something, they get a procedure error in this line:

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

我想Undo命令在德语中的命名是不同的.

I guess the Undo command is named differently in German.

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

Is there a way to define the Undo list independent of the language the user is using?

推荐答案

请改用控件的ID号.

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

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

这篇关于指独立于Excel Undolist语言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！